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

["Alex Turner" <armtuk@xxxxxxxxx>]

How do you normalize 90 arbitrary attributes away into subordinate tables?  There will still be 90 of them, you can split them up into multiple tables, but it would just make joins a pain and potentially bog down the query planner I would think.

Alex

On Feb 4, 2008 7:09 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:

On Feb 3, 2008 10:14 PM, Alex Turner <armtuk@xxxxxxxxx> 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?

But you're giving us a choice between two bad methodologies.

Properly normalized, you'd not have a table with 90 nullable columns,
but a set of related tables where you'd only need to store things in
the subordinate tables for the relative data points.

The worst thing about EAV is that it makes it very hard to figure out
what the heck is going on by just looking at the database schema.
It's easy to develop and hard to maintain.  We had a person do
something like that last place I worked and it took weeks for a new
developer to figure it out and replace it with a properly relational
model, because there were little corner cases all through the code
that kept popping up.