State park database: a Minimal Modeling approach (pt. III-b)
Author: Alexey Makhotkin squadette@gmail.com.
In the previous post we looked at the logical ERD diagram and tried to investigate if it is a good system specification tool.
We can also contrast ERD diagram and the tabular format proposed by the Minimal Modeling approach.
State park database: a Minimal Modeling approach (pt. III-b)
Here is my attempt to reproduce the above ERD diagram in a tabular format presented in the “Database Design Book”. Some parts are unclear to me and marked with “(?)”; unfortunately, the original system is also hypothetical, so there are no stakeholders who could tell us what’s the right way. ChatGPT output was not used here, so any mistakes are mine.
I’ve corrected some modeling mistakes that seem obvious to me. Only logical columns are presented.
Anchors
| Anchor | ID type |
|---|---|
| Facility | Lineid |
| Forest | Polygonid |
| ForestStand | Polygonid |
| FireStation | Pointid |
| Manager | ID |
| River | Lineid |
| Road | Lineid |
| Species | ID |
Here “ID type” is a non-standard data catalog column. We use it to capture the information from the ERD diagram that we want to better understand. We see that for the original authors it’s important that some objects are lines, some are polygons, and some are points. For anchors where this was not specified, we just use a generic “ID”.
I’ve added the Species anchor here.
Attributes
| Anchor / Attribute | Question | Example value | Logical type |
|---|---|---|---|
| Forest / name | What is the name of the Forest? | “Nantahala National Forest” | string |
| Forest / avg_elevation | What is the average elevation of the Forest (in meters)? | 1066.8 | number |
| Facility / name | What is the name of the Facility? | “Fires Creek picnic area” | string |
| FireStation / name | What is the name of the Fire Station? | “Nantahala Volunteer Fire & Rescue” | string |
| Manager / name | What is the name of the Manager? | “John Bartram” | string |
| Manager / date_of_birth | What is the date of birth of the Manager? | 1999-06-03 | date |
| Species / name | What is the name of the tree Species? | “maple” | string |
| Road / name | What is the name of the Road? | “Wayah Rd” | string |
| River / name | What is the name of the River? | “Cheoah River” | string |
| River — supplies_water_to — Facility / volume | What is the volume of River supply to the Facility (in m^3/h)? | 1200 | number |
I’ve omitted the “Manager / gender” because it is a very special case that often requires a separate discussion. (At the same time, it’s very common in teaching database schemas because it’s so simple technically.)
Note that here we have a link attribute (see the last row). Its name is awkwardly long, but unambiguous.
Also, instead of “Manager / age” I added a proper “date_of_birth” attribute. “Age” is also unfortunately very common in teaching databases, and it’s technically incorrect and misleading.
Links
Let’s try to write down plausible sentences for all links presented in the diagram.
| Anchor1 / verb / Anchor2 | Cardi- nality |
Sentences |
|---|---|---|
| Road / touch / FireStation | M:N | A Road can be adjacent to several Fire Stations A Fire Station can be adjacent to by several Roads |
| Road / crosses / River | M:N | A Road can cross several Rivers A River can be crossed by several Roads |
| Road / touch / Facility | M:N | A Road can be adjacent to several Facilities A Facility can be adjacent to several Roads |
| Road / accesses / Forest | M:N | A Road can access several Forests A Forest can be accessed by several Roads |
| Road / crosses / ForestStand | M:N | A Road can cross several Forest Stands A Forest Stand can be crossed by several Roads |
| River / supplies_water / Facility | M:N | A River can supply water to several Facilities A Facility can be supplied by several Rivers |
| River / touch / Facility | 1:N | A River can be adjacent to several Facilities A Facility can be adjacent to only one River |
| River / crosses / Forest | M:N | A River can cross several Forests A Forest can be crossed by several Rivers |
| Facility / within / Forest | N:1 | A Facility can be within only one Forest A Forest can enclose several Facilities |
| Facility / belongs_to / Forest | N:1 | A Facility can belong to only one Forest (?) A Forest can have several facilities |
| Fire Station / monitors / Forest | M:N | A Fire Station monitors several Forests (!) A Forest is monitored by several Fire Stations |
| Manager / manages / Forest | 1:N | A Manager manages several Forests (?) A Forest is managed by only one Manager |
| Forest / is_part_of / ForestStand | 1:N | A Forest can include several Forest Stands A Forest Stand can be part of only one Forest |
| ForestStand / consists_of / Species | 1:N | A Forest Stand comprises only one Species A Species can exist in several Forest Stands |
“Fire Station / monitors / Forest”: the original diagram clearly specifies 1:N cardinality (“A Fire Station monitors only one Forest”), but it just doesn’t make sense. I cannot imagine that a fire station placed between two forests won’t monitor both. This is a very good example of why cardinality in ERD diagrams is not very trustworthy. Writing down two sentences lets us clearly see that one of them does not make a lot of sense. There is a chance that this is true, but we need stakeholder confirmation on that. Maybe “monitors” means “formally assigned to”, or something? We cannot know without more business context, and the ERD diagram does not provide that context.
Note that “River / touch / Facility” is 1:N, while “Road / touch / FireStation” is M:N. This sounds plausible because you can imagine a fire station sitting on the crossroads, with multiple exits. But I find it hard to imagine a definite “crossrivers”. I may well be wrong, even in both cases! That’s why you need business stakeholders who understand the underlying reality.
Note that there are two different links between River and Facility: “touch” and “supplies_water”. I am not sure what that means, but the original authors probably knew what they were talking about.
Same with Facility and Forest: we have “belongs_to” and “within”. Maybe this is needed because you can have a facility that is outside of the forest, such as a visitor center?
I am completely not sure about “Facility / belongs_to / Forest” though. I wrote down 1:N cardinality, but having a facility that somehow belongs to several forests sounds plausible too.
“Manager / manages / Forest” is another link that raises suspicion. The original ERD diagram specifies 1:1 cardinality (“A Manager manages only one Forest”), but I find it hard to believe. Again, I may well be wrong.
Diagram
For reference, here is a simple diagram built in Arrows.app. It shows only anchors and links, all the attributes are omitted.
To me, this presentation seems to be an improvement over the traditional ERD diagram view. For a database schema overview, links are more important than attributes.
#
Subscribe to the “Database Design Book” newsletter to receive future posts about database design: https://databasedesignbook.com/.