Re: [SLUG] [OT]: Database Design Question

[Gavin Carr <gavin@xxxxxxxxxxxxxxxxx>]

On Tue, Aug 09, 2005 at 07:35:11PM +1000, Adam W wrote:
> E.g
> The parent table is "person" and it holds common data amongst all
> people - it has primary key of personID.
> There is another table assoicated with person called "personDetails".
> This would have fields "personID", "dataType" and "dataValue". It
> might have records like the following:
> personID, dataType, dataValue
> 1,phone,123456
> 1,email,test@xxxxxxxx
> 1,state,NSW
> 2,phone,987456321
> 2,state,VIC
> 3,phone,789456123
> 
> Of course this is just an example - the parent entity could be anything.
> 
> Hope someone can put a name to this sort of design. I want to research
> into this to see how people search effectively in this design and its
> performance compared to traditional methods etc etc.

I've used this type of design a number of times. I've heard it termed
'attribute tables', but I haven't been able to find much via google 
matching that term. It's a proper normalised representation though - 
both 3NF and BCNF, I believe.

As you imply, the main advantage of doing it this way is that you can
add new attributes to a object without having to change your schema - 
they just become additional rows. The disadvantage is that if you are
primarily going to want to deal with the data as a flat 'person'
record, then the joins do hit you on the performance side. Note that 
there are a number of use cases where you don't need to do that 
though - if you want to use the attributes primarily as search keys, 
for instance, then it can be a very efficient and flexible 
representation.

I confess that in one large real-world app I was involved with we
denormalised this part of the data model and did both - had the most 
common attributes as columns on the person record, _and_ as rows in 
the person attribute table. Slightly evil, but it worked out very 
well. ;-)

I'd be interested if you do turn up any discussion of this Adam.

Cheers,
Gavin