Re: Re-design of data storage table

From: Leif Neland <leif_at_neland.dk>
Date: Wed, 11 Mar 2009 14:33:21 +0100
Message-ID: <49b7bda3$0$56783$edfadb0f_at_dtext02.news.tele.dk>

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