Re: Re-design of data storage table
From: Ed Prochak <edprochak_at_gmail.com>
Date: Mon, 23 Feb 2009 06:14:48 -0800 (PST)
Message-ID: <ec7d92cf-2956-4676-b670-89b559d628d3_at_r22g2000vbp.googlegroups.com>
On Feb 23, 4:29 am, __Paul <paulwragg2..._at_hotmail.com> wrote: []
>
> > A common approach is to make the column rows. This is known as EAV,
> > Entity-Attribute-Value, and there is no shortage of people who points
> > finger at it. Personally, I think EAV is fine if used with care and in
> > only in special cases. EAV gives you lots of flexibility, but writing
> > complex queries is difficult their performance will be horrible.
>
> I discovered the correct name for EAV over the weekend - it does seem
> to be greatly frowned upon by a lot of DBA's.
> Our domain is clinical data - the database is for a product and not
> for an in-house system. Therefore it is imperative that users can add
> their own attributes without having to modify the database schema
> itself. I would guess this does qualify as a special case.
> The querying is the part that concerns me as some of the reporting is
> quite intensive as it is. I just cannot see of any other way around it
> except for using EAV.
>
[]
>
> I do understand relation database design. I am just not sure that this
> problem can be solved using a standard 'traditional' design.
>
> Thanks to everybody for their responses - I could not find much about
> this until I discovered the correct name for this design method. I
> think there is probably enough out there already without me having
> posted in the first place.
> Thanks again!
>
> Paul
Date: Mon, 23 Feb 2009 06:14:48 -0800 (PST)
Message-ID: <ec7d92cf-2956-4676-b670-89b559d628d3_at_r22g2000vbp.googlegroups.com>
On Feb 23, 4:29 am, __Paul <paulwragg2..._at_hotmail.com> wrote: []
>
> > A common approach is to make the column rows. This is known as EAV,
> > Entity-Attribute-Value, and there is no shortage of people who points
> > finger at it. Personally, I think EAV is fine if used with care and in
> > only in special cases. EAV gives you lots of flexibility, but writing
> > complex queries is difficult their performance will be horrible.
>
> I discovered the correct name for EAV over the weekend - it does seem
> to be greatly frowned upon by a lot of DBA's.
> Our domain is clinical data - the database is for a product and not
> for an in-house system. Therefore it is imperative that users can add
> their own attributes without having to modify the database schema
> itself. I would guess this does qualify as a special case.
> The querying is the part that concerns me as some of the reporting is
> quite intensive as it is. I just cannot see of any other way around it
> except for using EAV.
>
[]
>
> I do understand relation database design. I am just not sure that this
> problem can be solved using a standard 'traditional' design.
>
> Thanks to everybody for their responses - I could not find much about
> this until I discovered the correct name for this design method. I
> think there is probably enough out there already without me having
> posted in the first place.
> Thanks again!
>
> Paul
If you google for clinical database designs you'll find a lot of them use an EAV approach. I don't know if this is really necessary or that Clinical DB developers all repeat the same error. Apparently there is some research in this design specific to clinical applications. Honestly, I do not see the advantages, but I guess it beats a generic text field.
Good luck. Received on Mon Feb 23 2009 - 08:14:48 CST