Re: [OT] "advanced" database design (long)

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
>