Re: DB structure for logically similar objects in different

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461