Over the decades I’ve worked with many data modelers – and I’ve learned something key to building a great model that is not always intuitively obvious: spend at least 50% of your data modeling time on the relationships. Often modelers are so overly concerned with the entities, attributes and unique identifiers or keys they don’t allocate enough time for the proper questioning, construction and review of relationships. Therefore, a majority of the failed databases which I have seen where a data model was present had compromised the fundamental business requirements due to irrational relationships.

Any competent information systems professional can design entities, attributes and their unique identifiers or keys – because it is essentially nothing more than choosing record layouts, which our industry has been doing since the beginning. So while we may find a missing key or index here and there that would improve performance (i.e. efficiency), it is improper relationships which most often compromise the business requirements (i.e. effectiveness). So spending extra time getting the relationships correct is time well spent.

Let’s look at some silly examples to demonstrate just how powerful relationship lines are to the fundamental business concept being modeled. While it may not be either the safest or most politically correct subject to model – I’m going to show how something as simple as marriage can be perverted by simply drawing the wrong line or relationship modifiers.

Look at this first ER diagram, here we have our first (failed) attempt at data modeling the basic concept of marriage.

What we have here is a very sexist model by western standards. This model says that a man must have one or more wives and that a woman must be married to a man. I don’t think either concept “flies”, because being single is always an option for either gender. Thus we try to correct it as below:

Of course this is really no better – we’ve simply modeled the “Old Testament” concept of marriage: men or women may be single, but men can have multiple wives. I very clearly comprehend what fundamental business rules this breaks from my wife’s viewpoint J

So we try to fix the model once again as now shown:

Oops - now we’ve basically reversed things and data modeled Greek Mythology’s “Amazonian” concept of marriage (and I don’t mean Wonder Woman here, but the legendary nation of female warriors from Homer’s Iliad – and I don’t mean Homer Simpson either). So now the women can have multiple husbands, and quite importantly - men must be married to (be the property of) some woman. Now maybe turn about is fair play – but not to many of us guys could tolerate this arrangement either.

So now we try one more time to model the simple western concept of marriage as shown:

Looks correct now – right? Either men or women can be single – and neither men’s nor women’s existence depends upon the other gender. Whew. OK – but what if one gets married several times during their adult life? Woops – our relationship now only shows the current situation. Thus all history is totally lost – not a concept that many businesses would embrace.

OK – so we try this once again. Now logically what we’re expressing is that marriage’s relationship is many-to-many (i.e. over time). So we now model as follows:

Note – if you’re wondering why this diagram looks slightly different, it’s because I am using Toad Data Modeler 3.0 (not out yet) – which offers logical modeling.

But any reasonable database person will inform you that it is quite physically impossible to implement many-to-many relationships via simple foreign keys. Because unless you implement those foreign keys on both sides using complex, multi-value data types like Oracle’s “nested tables” or “varrays” – you cannot enter the data. And using complex data types like this violates first normal form – no repeating groups (i.e. arrays).

So most data modeling tools will force the physical model of the above logical construct to be correctly and relationally implemented via a “bridging” or “intersection” entity, as shown below.

OK – we’re getting closer. Now we can have either single men or women. A marriage must and can consist of only one of each gender. And we can now maintain accurate history by simply adding begin and end date attributes to the MARRIAGE entity. Plus for additional correctness, we can add a pre-insert database “trigger” to make sure that no person is active in two marriages at the same time (i.e. no overlapping dates for each person’s records).

Guess what – we’re still not done yet. Now we have this modern political issue of same sex marriages. Now I’m not offering any position here – I’m merely trying to make sure my data model could handle that scenario if necessary. So obviously all the above models are flawed for this additional requirement. Hmm – what to do? How about this.

This technique is one of the most powerful tools or constructs in data modeling: that of roles and assemblies. This topic is far too complex to detail in its entirety, but basically we utilize fourth normal form to build complex relationships between entities (i.e. those relationships that are either hierarchical or repeatable). For example if we wanted to add the concepts of “best man” and “maid of honor”, we simply would add two more simple relationship lines to the model above. What we’re showing is that for the occurrence of the entity MARRIAGE, there can be many participants.

Of course we’ll still need some additional database pre-insert “triggers” to handle some additional business requirements. For example, one cannot play more than a single role in any given marriage.

So now we’ve taken something as simple as marriage and had to model it multiple times before we got it right. And in nearly every case the differences were primarily due to the relationships, their optionality and cardinality. We never really considered any attributes or keys (except possibly for MARRIAGE begin and end dates), and yet we data modeled widely differing concepts.

Trust me – pay close attention to every detail of all your relationship lines: because your company’s business is surely much more complex than what we thought was the simple concept of marriage.