Modeling a Cookie
The tool does allow me to record business rules, and I've customised the diagram to show a list of the rules attached to my relationship (see attached) - I wouldn't really want them on the diagram (too much clutter), possibly just some indication that there are rules to look at.
Ah, so we have a classic many-to-many relationship, which we will eventually resolve into an entity and two relationships, or possibly something more complicated. Here are a couple of possible quick solutions in my favourite tool.
1. the rule about the number of players in a team is held as attributes on a new entity called 'Sport' - there's also an entity called 'Team Member'. Both of these entities are linked to a validation rule that describes the actual business rule.
2. I've introduced the 'Season' entity and used a different notation, in which I show that 'Team Member' is an Association of Player, Season and Team.
Intriguing thread. Modeling tools have fallen short for me as well, in allowing full expression of business/data rules and data evolution.
When designing non-trivial models in a modeling tool, I'll often attach a Word doc or Excel doc, that I call the "Functional Data Model". It breaks the model down into clusters of functionally related tables. For each cluster, the document will use screenshots of submodels from the ERD for reference and visual interest. It will detail things that are awkward to document in table/column comments, like:
- data conditions and rules that span multiple tables
- things like 2 or more ingredients, or the 6 to 12 player cardinality mentioned earlier
- assumptions, caveats, rejected design decisions and why they were rejected
- how rows of data are "born" and change over time in certain tables, showing sample rows of data at its various stages of progress
Getting the data designer's inner thoughts and memories out onto paper has been very useful, especially 2 to 5 years later when few are left who originally built the system. It has also really helped my engineers to see the designer's vision and assumptions about how data looks within the model. Just seeing the empty shell of the model in the ERD, along with optionality, constraints, relations and defaults doesn't quite explain all that needs to be known about how the data structures are meant to be used.