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.
It all seems to boil down to "how to model it?". But that is not the problem in itself, it can be modeled. The real question then is "How to model ánd visualize it in a diagram which has no support for it." Like @Dirk states, perhaps use UML. As @Stefan states, use a different/extra tool or documentation. Workarounds or additional documentation will simply required if the used diagram/tool/method doesn't support it.
From the modeling perspective my question is this: Why note the ingredients at all? Is this for the customer to review the ingredients, or is this meant for a recipe? This should be taken into account to even be able to discuss the case, for the data is differently managed. Similar to business lunch receipts and the final accounting of it. The book is not the problem, perhaps the use-case is a little over-simplified.
In regards to @George Team issue it shouldn't be too hard to model a Played-Team as a many to many relationship. A database constraint enforcing the 6 or 12 team members should be documented and enforced in the implementation. These business rules should be documented/shown at the Team Member level (either an association table, relationship, or otherwise..)
We probably all know how to do this, but it remains a matter of presentation and safeguarding such 'complicated' business rules. In respect of teams I remember the following simple question: How to model the "Team of George, Stephan and Marco"? Simple questions, simple rule, complicated to model without adding artificial constructs.
Always build in room to document more.