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

On Feb 3, 2008 11:14 PM, Alex Turner <armtuk(at)gmail(dot)com> wrote:
> 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?

Definitely not. 90 null values will require about 12 bytes of memory
to represent their absence in the "all in one" table. That's not very
much space.

In contrast, if you need to join out to 80 tables, possibly folded
into some smaller number, you'll *at least* have an index scan,
reading a few pages of data from the secondary table, and then need to
read the pages containing those values that *are* joined in.

That quickly grows to way more than 12 bytes :-)

--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling