On May 30, 2006, at 5:48 AM, Eci Souji wrote:
Hmmm that works too. So I guess my next question is which is a better designed system; one large table with bools and views or six small tables with stored procs to move data between tables?
That depends entirely on your access patterns and how your data is broken down. Moving data between tables will be more involved from a code standpoint, and thus more prone to errors. On other databases it would also be less efficient, but because of how PostgreSQL does MVCC I don't think it would make too much of a difference performance-wise.
You also need to consider the breakdown of your data. If you've got one set of conditions that are very prevalent, you can see some storage (and hence, speed) gains by splitting into different tables, perhaps by having one table for the common case and another one that handles all the uncommon cases. For example, if you have a users table, if you have a very large number of users it will probably help to have a seperate user_lockout table that contains only the user_id of users that are denied access to the system. The downside is that you have to do a join every time you want to check that. The upside is that you're saving as much as 4 bytes in the user table, which depending on how many users you have and your access patterns can add up.
-- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
