|
|
Posted by mittal.pradeep on 09/26/05 22:02
CELKO,
If I change the data elements 10 times during the year, I cannot change
the front end 10 to accomodate the same. I need to come up with a
dyanamic solution to take care of same. Vertical approach is the best
way to get the same. Using custom columns is a compromise, as vertical
approach is harder to implement.
--CELKO-- wrote:
> Neither. The design flaw you are calling a vertical model is actually
> known as "EAV" or "Entity-Attribute-Value" because it is a common
> newbie mistake. I have no idea what your #2 means.
>
> As your data elements change, you need to re-design the schema --
> constraints, keys, data types, etc. Learn RDBMS and do it right.
>
> I found an old "cut & paste". Someone like you posted this:
>
> CREATE TABLE EAV -- no key declared
> (key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL);
>
> INSERT INTO EAV VALUES ('LOCATION','Bedroom');
> INSERT INTO EAV VALUES ('LOCATION','Dining Room');
> INSERT INTO EAV VALUES ('LOCATION','Bathroom');
> INSERT INTO EAV VALUES ('LOCATION','courtyard');
> INSERT INTO EAV VALUES ('EVENT','verbal aggression');
> INSERT INTO EAV VALUES ('EVENT','peer');
> INSERT INTO EAV VALUES ('EVENT','bad behavior');
> INSERT INTO EAV VALUES ('EVENT','other');
>
> CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
> (id INTEGER IDENTITY (1,1) NOT NULL,
> bts_id INTEGER NULL,
> key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL );
>
> INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
> INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
> INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');
>
> Ideally, the result set of the query would be Location Event count
> (headings if possible)
>
> Bedroom verbal aggression 1
> Bedroom peer 0
> Bedroom bad behavior 0
> Bedroom other 2
> Dining Room verbal aggression 0
> Dining Room peer 0
> Dining Room bad behavior 0
> Dining Room other 0
> Bathroom verbal aggression 0
> Bathroom peer 0
> Bathroom bad behavior 0
> Bathroom other 0
> courtyard verbal aggression 0
> courtyard peer 1
> courtyard bad behavior 0
> courtyard other 1
>
> Also, if possible, another query would return this result set. (I think
> I know how to do this one.)
>
> Location Event count
> Bedroom verbal aggression 1
> Bedroom other 2
> courtyard peer 1
> courtyard other 1
>
> Here is a From: Thomas Coleman
>
> SELECT Locations.locationvalue, Events.eventvalue,
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
>
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event'
> ) AS EventData
> ON LocationData.bts_id = EventData.bts_id
> ) AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events
> ORDER BY Locations.locationvalue, Events.eventvalue ,
> SELECT Locations.locationvalue, Events.eventvalue
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
>
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event') AS EventData
> ON LocationData.bts_id = EventData.bts_id)
> AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue)
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events;
>
> Is the same thing in a proper schema as:
>
> SELECT L.locationvalue, E.eventvalue, COUNT(*)
> FROM Locations AS L, Events AS E
> WHERE L.btd_id = E.btd_id
> GROUP BY L.locationvalue, E.eventvalue;
>
> The reason that I had to use so many subqueries is that those entities
> are all lopped into the same table. There should be separate tables for
> Locations and Events.
>
> The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the
> end of the column name is really non-intuitive. I removed them for my
> example and came across the next column name faux pas. Don't use "key"
> and "value" for column names. It means that the developer *has*
> surround the column name with square brackets for everything which is a
> serious pain.
>
> There is such a thing as "too" generic. There has to be some structure
> or everything becomes nothing more than a couple of tables called
> "things". The real key (no pun intended) is commonality. Is there a
> pattern to the data that they want to store? It may not be possible to
> create one structure to rule them all and in the darkness bind them.
>
> "To be is to be something in particular; to be nothing in particular is
> to be nothing." --Aristole
>
> All data integrity is destroyed. Any typo becomes a new attribute or
> entity. Entities are found missing attributes, so all the reports are
> wrong.
>
> ry to write a single CHECK() constraint that works for all the
> attributes of those 30+ entities your users created because you were
> too dumb or too lazy to do your job. It can be done! You need a case
> expression almost 70 WHEN clauses for a simple invoice and order system
> when I tried it as an exercise.
>
> ry to write a single DEFAULT clause for 30+ entities crammed into one
> column. Impossible!
>
> Try to set up DRI actions among the entities. If you thought the WHEN
> clauses in the single CASE expression were unmaintainable, wait until
> you see the "TRIGGERs from Hell" -- Too bad that they might not fit
> into older SQL Server which had some size limits. Now maintain it.
>
> For those who are interested, there are couple of links to articles I
> found on the net:
>
> Generic Design of Web-Based Clinical Databases
> http://www.jmir.org/2003/4/e27/
>
> The EAV/CR Model of Data Representation
> http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm
>
> An Introduction to Entity-Attribute-Value Design for Generic
> Clinical Study Data Management Systems
> http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems..htm
>
>
> Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
> Database
> http://www.pubmedcentral.nih.gov/articlerender.fcgi?tool=pubmed&pubme...
>
>
> Exploring Performance Issues for a Clinical Database Organized Using
> an Entity-Attribute-Value Representation
> http://www.pubmedcentral.nih.gov/articlerender.fcgi?tool=pubmed&pubme...
Navigation:
[Reply to this message]
|