From: | "Alex Turner" <armtuk(at)gmail(dot)com> |
---|---|
To: | "Lewis Cunningham" <lewisc(at)rocketmail(dot)com> |
Cc: | "vladimir konrad" <vk(at)dsl(dot)pipex(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [OT] "advanced" database design (long) |
Date: | 2008-02-04 04:14:24 |
Message-ID: | 33c6269f0802032014i3878ec3co4488b4835ef1e3d8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I"m not a database expert, but wouldn't
create table attribute (
attribute_id int
attribute text
)
create table value (
value_id int
value text
)
create table attribute_value (
entity_id int
attribute_id int
value_id int
)
give you a lot less pages to load than building a table with say 90 columns
in it that are all null, which would result in better rather than worse
performance?
Alex
On Feb 2, 2008 9:15 AM, Lewis Cunningham <lewisc(at)rocketmail(dot)com> wrote:
>
> --- vladimir konrad <vk(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 Technology
> http://blogs.ittoolbox.com/oracle/guide/
>
> LewisC's Random Thoughts
> http://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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2008-02-04 04:22:45 | Re: PostgreSQL Certification |
Previous Message | Joshua D. Drake | 2008-02-04 04:05:53 | Re: PostgreSQL Certification |