Pinderkent

Today I read some very true observations from Peter Harkins about the nature of aging databases. He mentions three such observations in his article, and other readers have contributed several more in the comments for that article, as well as at other sites like Hacker News. I'd like to specifically focus on his second observation, which he entitles "All Relationships Become Many-to-Many."

This is quite true. I must admit, I have seen this happen with numerous different databases in a wide variety of domains. The mere fact that it arises so often should make us reconsider how we think of certain relationships. What we often find is that real-world considerations do force a large number of relationships to become many-to-many. Although the specification indicated that wouldn't be the case, specs are often incomplete and sometimes are just plain wrong.

One option that should be considered is treating all relationships as many-to-many. Although it brings in a level of complexity, it can help avoid the after-the-fact database-level hacks that are often necessary to allow for such relationships to be stored. Arguably, the hacks are worse than the complexity brought in by always dealing with many-to-many relationships.

If our data layer treats all relationships as many-to-many, we can take advantage of this in our business logic. Here we can reduce the multiplicity of the relationships, if necessary. But if later on we find that we do need to support many-to-many relationships once we start dealing with realistic data, we will probably be in a better position to make such changes. Instead of cascading up from the database through to the UI, we're only making changes at the topmost layers of an application if the database already supports many-to-many relationships.

The more we fight with reality, the more we will run into problems like this. So perhaps assuming the "worst", that is to say that all relationships are many-to-many, is the best course of action. In will be setting us up to fail in the softest way possible, with changes happening in volatile business layer logic, rather than in the structure of our database tables.