Re: "advanced" database design (long)

From: SunWuKung <Balazs(dot)Klein(at)t-online(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: "advanced" database design (long)
Date: 2008-02-08 00:20:20
Message-ID: 31b7eb82-ba34-4036-9f9d-fb3dfa812737@q77g2000hsh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On febr. 2, 15:15, lew(dot)(dot)(dot)(at)rocketmail(dot)com (Lewis Cunningham) wrote:
> --- vladimir konrad <v(dot)(dot)(dot)(at)dsl(dot)pipex(dot)com> wrote:
>
> > I think that I understand basic relational theory but then I had an
> > idea.
> > Basically, instead of adding field to a table every time there is a
> > need for it, have a table split in two: one holds identity (id) and
> > one holds the attributes (linked to this id).
> > Basically, if in the future user decides that the subject should
> > have a new attribute, he can simply add "attribute definition" and
> > attribute_definition_set (if any) and the application would handle
>
> Basically, you would be creating your own data dictionary (i.e.
> system catalog) on top of the db data dictionary. The database
> already comes with a way to easily add columns: ddl. I have seen
> newbie database designers reinvent this method a hundred times. The
> performance hits and complexity of querying data would far out weigh
> any perceived maintenance gain.
>
> My .02.
>
> LewisC
>
> Lewis R Cunningham
>
> An Expert's Guide to Oracle Technologyhttp://blogs.ittoolbox.com/oracle/guide/
>
> LewisC's Random Thoughtshttp://lewiscsrandomthoughts.blogspot.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

I always thought that having nullable columns in a table is a Bad
Thing (http://technet.microsoft.com/en-us/library/ms191178.aspx) and
shows that you try to put different type of entities into the same
table - having 90 in a column ... brrrrr.
I think its much better to avoid it whenever you have the info but
when you don't you just have to use the EAV model.
E.g. If I knew what info I wanted to store on a person I could create
columns for that, but since in our application users create the
questionnaires that is used to store info on persons I see little
choice - I must have a subjectID, questionID, value table.

SWK

SWK

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Fitzpatrick 2008-02-08 00:37:45 Test text value as interval
Previous Message Tom Lane 2008-02-08 00:06:50 Re: using DROP in a transaction