Skip site navigation (1) Skip section navigation (2)

Re: DB structure for logically similar objects in different

From: Jim Nasby <jnasby(at)pervasive(dot)com>
To: Eci Souji <eci(dot)souji(at)gmail(dot)com>
Cc: Roman Neuhauser <neuhauser(at)sigpipe(dot)cz>, pgsql-general(at)postgresql(dot)org
Subject: Re: DB structure for logically similar objects in different
Date: 2006-05-30 19:16:14
Message-ID: 4345A545-B553-4B76-9A30-3A3D5DF2A834@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-general
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



In response to

pgsql-general by date

Next:From: Tom LaneDate: 2006-05-30 19:38:55
Subject: Re: [GENERAL] 8.1.4 - problem with PITR - .backup.done / backup.ready version of the same file at the same time.
Previous:From: Rafael MartinezDate: 2006-05-30 18:21:32
Subject: Re: [GENERAL] 8.1.4 - problem with PITR - .backup.done /

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group