Re: "advanced" database design (long)

On febr. 2, 15:15, lew...@rocketmail.com (Lewis Cunningham) wrote:
> --- vladimir konrad <v...@dsl.pipex.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