Tugger the SLUGger!SLUG Mailing List Archives

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


Gavin,

Thanks for your input. Much Appreciated.

On 8/10/05, Gavin Carr <gavin@xxxxxxxxxxxxxxxxx> wrote:
> 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
...
> 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.

Done some searching on "Attribute Tables" and after some serious
googling i've found another name for this sort of design.
Entity-Attribute-Value or EAV. If you search on google you will find
many papers published about this type of design used in the medical
information systems.
An example:
http://www.pubmedcentral.nih.gov/articlerender.fcgi?artid=346624

Thanks,

Adam.