Re: Re-design of data storage table

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
> row).

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.
Thanks again!

Paul Received on Mon Feb 23 2009 - 03:29:24 CST