SLUG Mailing List Archives
Re: [SLUG] [OT]: Database Design Question
- To: slug@xxxxxxxxxxx
- Subject: Re: [SLUG] [OT]: Database Design Question
- From: Gavin Carr <gavin@xxxxxxxxxxxxxxxxx>
- Date: Wed, 10 Aug 2005 08:41:19 +1000
- Organisation: Open Fusion
- User-agent: Mutt/1.4.1i
On Tue, Aug 09, 2005 at 07:35:11PM +1000, Adam W wrote:
> 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
> 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
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
I'd be interested if you do turn up any discussion of this Adam.