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
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 |