Re: Re-design of data storage table
Date: Mon, 23 Feb 2009 01:29:24 -0800 (PST)
First off, thanks to everybody for their replies. As I mentioned earlier, I had already come up with a method of doing this but did not want to influence peoples replies. As it turns out it looks like I have come up with a modified version of EAV. I say modifed because rather than storing each attribute/value item in a single table I have a different table for each data type. Anyway, some responses below:
> Why do you need to add new attributes as a column rather than use a
> name value pair where each attribute is just another row of data
> (name, value, unit of measure, whatever else is need to work with
This is what I had come up with myself but I really was not convinced whether it was the best solution. Thanks for your response.
> Your only real alternate from your description is to go ahead and
> modify your application so it can support hundreds of columns in the
> data table.
There really is no way that we want to do this!!
> 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.
> A very orthodox approach is to make a proper database design from the
> start, but that system may not be very flexible.
I assume by this you mean having all the attributes defined in the system from the beginning? Now I have explained briefly what the domain is hopefully it is clear why we cannot really fix the attributes. Sorry if I did not give enough information to start with.
Mxyplx - thank you for re-enforcing the hideous queries that result from such a design.
> I think you need to start with studying and understand ERD and
> relational database design. Scalable applications use real models of
> the world.
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.
Paul Received on Mon Feb 23 2009 - 03:29:24 CST