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@test.com > 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 -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
