Modeling a Cookie
In the recently published book, The Rosedata Stone by Steve Hoberman, the author is describing a model for a cookie: "...a Cookie must contain one or many Ingredients..." (author emphasis). While by the model, this is an accurate description, I feel that it is unrealistic.
Who wants to eat a "cookie" with one ingredient? A butter cookie with just butter (an example from the book) is just a stick of butter, not a cookie. Even if you use a cookie cutter to shape it like a cookie, it's still just butter. Perhaps it would be tasty if the only ingredient was a chocolate chip, but even that is not really a cookie.
Please don't misunderstand - the book is a good book and I highly recommend it. However, I have pulled out this one example to show what I perceive as a limitation of some models. In reality, a cookie must contain two or more ingredients, not just one.
So, how do we model that last statement - two or more? Can we? Do we have to rely on some additional documentation in order to get a full picture of what a cookie (or any other thing) really is? Business rules, a dictionary or glossary?
I have heard some folks say that a data model is all you need to describe a business concept. I disagree. I feel that data models are very useful but that, for the most part, something is always missing if we go with only the data models.
In this case I would use the entity Ingredient as supertype. Creating subtypes (not exhaustive and not complete) to classify different types of ingredients. If the business case is a backery there are mandatory incredients for all (?) products (e.g. cookie) which are produced, e.g. flour, liquid, spice and others which are not mandatory like toppings. So, the relation is designed from product (cookie) to ingredients subtypes, not to ingredients. Now it is possible to model at least how many ingedients are needed/mandatory to create a product. And I would add an entity recipe which brings together all ingredients for one single product.
What do you think?
@dirklerner Sorry it took a while to respond. I have been recovering from a hard drive crash.
I thought about adding recipe, but realized that it would have a similar problem. In the model, it would translate to: Each Recipe must have one or many Ingredients, when, in reality, each Recipe must have two or more Ingredients.
In the book, the business case was a bakery. I don't think we can have mandatory ingredients for all products, such as a cookie, a pastry, and a cake, but you might have mandatory ingredients for all cookies, then optional ingredients that makes each type of cookie unique. Perhaps a supertype of products (cookie, pastry, cake). Then, for each product type, a supertype that would describe the type of ingredients for each specific product (butter cookie, chocolate chip cookie, oatmeal raisin cookie, etc.).
The author of the book did not introduce super/sub types. I suspect that he was showing things, initially at least, at a conceptual level. Super/sub types tend to be introduced at the logical level.
Nonetheless, I think the classifying of ingredients and then typing them could be beneficial. I think it would come down to the need to explain what the overall model means to the interested parties at the appropriate levels.
each Recipe must have two or more Ingredients.
A volleyball team consists of players. It can only consist of 6-12 players. This cannot (or better should not) be modeled with entities and relationships alone. So in the grafical ERD, you can not show this business rule.
Of cource the restriction (busines rule) has to be documented in the model somewhere (in this case on the relationship between team an player) Otherwise it is not guaranteed to end up in your database or software. A datamodel must allow business rules to be defined. But not necesseraly in the in the ERD.
Some modelling tools allow to specify the many of a relationship end as minimum .. maximum. This would solve the currently discussed problem. But there are more types of important business rules, so you need some mechanism for business rules documentation anyway.
To resolve the recipe-ingredients-problem I would use the business rules solution. If the information is essential for any reader looking at the diagram, I would put a note next to the relationship, explaining the special circumstances.
I can show the cardinality limits in my favourite modelling tool (see attached), though it would be tricky to show more complicated conditions (such as team membership size varying according to the type of sport) on the diagram.