Re: Re-design of data storage table
Date: Wed, 11 Mar 2009 14:33:21 +0100
An idea would be a kind of semi-EAV, where most of the attributes were in a main table, and the rest was in a supplementary eav-table.
And then a layer, interpreting the queries, knowing if the attribute was in which table.
I think it should be possible automagically to make a view containing the required fields.
CREATE VIEW userview_123 AS
SELECT animals.animal_id,height, weight, at1.eav_val as spots, at2.eav_val as eyecolor FROM animals
LEFT JOIN attribs at1 ON at1.eav_key='spots' AND at1.animal_id=animals.animal_id LEFT JOIN attribs at2 ON at1.eav_key='eyecolor' AND at1.animal_id=animals.animal_id
It would probably be tricky to write this layer to handle inserts/updates/joins, but when this was done, this layer would hide the EAV, and the system could periodically be analyzed and attributes could be moved in and out of the supplementary table as deemed the most efficient.
A nice idea for a Computer Science project :-)
Leif Received on Wed Mar 11 2009 - 08:33:21 CDT