Re: "advanced" database design (long) - Mailing list pgsql-general

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org
> so that your
>        message can get through to the mailing list cleanly
>
>
I use it in the same manner : at the time of recording, I just know one
kind of entity (words) with a value as varchar. Maybe better with xml,
but i use for long time to do the following job with SQL.