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

["Alex Turner" <armtuk@xxxxxxxxx>]

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@xxxxxxxxxxxxxx> wrote:

--- vladimir konrad <vk@xxxxxxxxxxxxx> 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