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

I just thought of another problem, the system can have multiple values
for a single attribute.  How do you normalise that without basically
adding a link table that's just the same thing as given below (I know
there are array types in Postgresql, but there aren't in other DBs and
I'm a fan of keeping products as DB neutral as possible)?

Alex

On Feb 4, 2008 7:09 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Feb 3, 2008 10:14 PM, Alex Turner <armtuk@gmail.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?
>
> 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.
>