opc.three (10/23/2012)

---

Eric M Russell (10/23/2012)

---

What's described above would be one form of a "property bag" technique. I have been tasked with building data extract and reporting from a large ISV database (PegaSystems) on Oracle that used this technique. From what I've been told, it's efficient from the application's perspective, because they can pull up a single patient's chart with only a few page reads. However, for aggregate reporting, the performance was bad, like full table scans. The developers actually had to replicate normalized versions of the tables just to facilitate the reporting queries.

Yep, totally agree. That's exactly where with EAVs and these type of property bag models fall down. They seem like a great idea at first because the developer making the choice is building an OLTP app..."it's so elegant, it can handle any type of property anyone could ever imagine, and it performs well, what could be better than this?" Until the poor soul that actually has to access more than one row at a time (reporting, batch processing, searching, etc.). I have been that poor soul, hence the soapbox 😀 On the other hand I have been paid well to undo messes like this :satisfied:

In the later versions of both Oracle and SQL Server, it's possible to index XML columns, which would make it more scalable as far as querying, but I still prefer fully normalized table design. Fortunately I don't get many requests for tables that allow the users to create their own attributes or "fields" on the fly.

I have not worked with an XML property bag implementation as described but have worked with XML indexes a bit and they have their own set of baggage to consider.