opc.three (10/23/2012)
Steven Willis (10/23/2012)
opc.three (10/22/2012)
This is just a pared-down example of a method used by one of the largest open-source development platforms in existence.
And that platform would be...care to share?
...and no, I don't work for DNN. But it is our primary development platform.
Thanks for sharing. That is not surprising. By nature DNN is built to handle structured, semi-structured and unstructured data so using an EAV may make sense. I would place that in the niche I mentioned. Most of what they store is likely unstructured, loosely typed metadata or document/site artifacts, not necessarily data domain elements. In my opinion when you have command over your data domain and need to store strongly typed data elements you should do everything in your power to avoid employing an EAV model (a non-relational concept) in a relational database. What is thought to be saved initially by implementing an EAV is paid for dearly when it comes time to work with that data in a tabular way, the definition of an anti-pattern. Just my two cents.
edit: spelling
OH, YES! I strongly agree! That little bit of "flexibility" established in DNN 1.0 from the start has caused me many headaches all the way up the the current v6.xx. When doing custom development within the DNN framework I avoid the ProfilePropertyDefinition table if at all possible and just create my own custom table(s) for the particular requirement.
My example above actually shows one of the major problems with the approach whereas I often have to use a PIVOT operator to get the necessary results. But very often I get handed a pre-existing site in which the previous admin has used the native definition tables and it would be more work to change it than to just apply the procedures I've collected over the years like my pivot query above. A pivot seems simple once you see a good example, but TOO MANY TIMES TO COUNT I've seen something like this:
SELECT
*
FROM
(
SELECT up.Value
FROM User u
INNER JOIN UserProfile up ON u.UserID = up.UserID
INNER JOIN ProfilePropertyDefinition ppd ON up.PropertyID = ppd.PropertyID
WHERE u.UserID = 12345 AND ppd.PropertyName = 'FName'
) AS FName
,(
SELECT up.Value FROM User u INNER JOIN ..... etc
) AS LName
,(
SELECT up.Value FROM User u INNER JOIN ..... etc
) AS City
,(
SELECT up.Value FROM User u INNER JOIN ..... etc
) AS State
/*** repeated dozens of times for all the profile items...(!) ***/
) AS Result
etc
Then, to make things even worse code like this will sometimes be wrapped by a cursor looping through all of the UserIDs! :blink:
Ultimately, I don't think such a construction is necessarily always bad (an EAV that is, not the bad query above)--sometimes they can be very useful. But it's a different kind of data relationship that takes some thought to build efficient queries.
Another construct I'd put in this category is something like a hierarchical Product table with multiple parent-child levels. That has its pros and cons as well--but writing EFFICIENT queries to build proper hierarchies can be a real PIA. 😉