There are a few data design patterns that are easy to get hooked on because of their flexibility. Like any tool, the value diminishes if it is used incorrectly, or where not needed. Here are some of my favorites that have a place in database design; just not all of the time.
EAV – EAV stands for Entity Attribute Values. It can be thought of as a key value pair, or property bag, that can be part of a real database entity (table). This allows you to add virtual columns to a table without modifying the schema. In fact, what you are doing is implementing your own column definitions using a table structure. This can be abused because it allows you to not fully understand your data domain. Because this is run time data definition, the performance is reduced. Moreover, consuming the data is more complicated as well.
Hierarchy – Everybody knows what a hierarchy is. We use them all the time working with file structures. You can create a hierarchical data structure in a database. They are really cool. They are really powerful. I have seen some cases where a hierarchy was used instead of creating real entities with relationships instead of hierarchical parents. In fact, you could build a complete database out of a hierarchy, if you took the concept to the ultimate extreme implementation. Performance would be horrible. But, it could be done.
Type/SubType – Type/Subtype is a method of allowing tables with different columns share a common concept. In fact, this is what you would do if you wanted create an entire database without relationships except those found in a hierarchy. If every table in your database shared a single parent type, or sequence, then they could all be a node in a hierarchy, and be combined in any order/relationship you wanted. While this pattern allows dissimilar entities to share common entities such as address, phone, mail, URL, etc., this isn’t always the best design. Sometimes the redundancy of having an address table for companies and people is a better design.
What’s your take on this? Do you have any guidance you’d like to share on these data structures? Are there other structures we should bring into the conversation? Please leave a comment with your thoughts.
Cheers
Ben