--- title: 'From Model to Schema' source: 'https://academia.sh/en/courses/modeling-and-representation/from-model-to-schema' course: 'Modeling and Representation' language: en updated: '2026-08-17T18:08:16+00:00' license: 'CC BY-SA 4.0' --- # From Model to Schema For seven tables and twenty-five fields, 100 field decisions and 14 table decisions are given: a total of 114, 4.4 times the 26 facts the conceptual model states. Every decision the model does not state is a decision someone makes that does not appear in the model. The previous lesson counted thirteen fields appearing at the logical level without their name ever occurring in the model, and showed that rescuing a dropped constraint runs, again, through adding a field not present in the model. This lesson descends to the final level. The physical schema makes the logical model storable. Its question is: how many decisions does this conversion require, how many of these decisions does the conceptual model determine, and who determines the ones it does not. The answer produces the course's largest ratio, and it makes the course's rule pay out one last time: **a notation's measure is not the symbols it carries but what it leaves to someone else because it does not state it.** ## The Questions the Physical Level Asks Making a field storable requires answering four questions: which **type**, what **length**, is it **nullable**, what is its **default value**. Making a table storable adds two more questions: an **index** for the primary key and a **character set**. None of these six questions is optional; if no answer is given, a default takes its place, and a default is an answer too. - **VM14** — At the physical level, every field asks four decisions, every table two. The list is the common definition and is not expanded in this lesson. - **VM15** — A decision being **invented** means no fact of the conceptual model determines that decision. An invented decision is not a wrong decision; it is a decision **whose source is not in the model**. ```python """M01/K06 common definition (excerpt): the number of decisions invented by the physical level.""" FIELD_DECISION = ["type", "length", "nullability", "default value"] TABLE_DECISION = ["primary key index", "character set"] LOGICAL = { "WorkOrder": ["key", "number", "opened", "status", "Technician_key"], "Technician": ["key", "name", "specialty"], "Part": ["key", "code", "name", "unit"], "Machine": ["key", "code", "type"], "Document": ["key", "type", "date", "WorkOrder_key"], "WorkOrder_Part": ["key", "WorkOrder_key", "Part_key"], "Technician_Machine": ["key", "Technician_key", "Machine_key"], } CONCEPTUAL_FACTS = 26 # 5 entities + 12 attributes + 4 relationships + 5 constraints def physical(tables): field = sum(len(v) for v in tables.values()) table = len(tables) return {"table": table, "field": field, "field_decision": field * len(FIELD_DECISION), "table_decision": table * len(TABLE_DECISION), "invented_decision": field * len(FIELD_DECISION) + table * len(TABLE_DECISION)} P = physical(LOGICAL) print("table fields field decisions table decisions total") for name, fields in LOGICAL.items(): fd = len(fields) * len(FIELD_DECISION) td = len(TABLE_DECISION) print(f"{name:19s} {len(fields):4d} {fd:11d} {td:12d} {fd + td:6d}") print(f"{'total':19s} {P['field']:4d} {P['field_decision']:11d}" f" {P['table_decision']:12d} {P['invented_decision']:6d}") print() print("facts the conceptual model states:", CONCEPTUAL_FACTS) print("decisions the physical schema invents:", P["invented_decision"]) print("ratio:", round(P["invented_decision"] / CONCEPTUAL_FACTS, 4)) print() print("even if every decision has only two options, the number of physical schemas") print("corresponding to the same logical model is:", 2 ** P["invented_decision"]) print("digit count:", len(str(2 ** P["invented_decision"]))) ``` ``` table fields field decisions table decisions total WorkOrder 5 20 2 22 Technician 3 12 2 14 Part 4 16 2 18 Machine 3 12 2 14 Document 4 16 2 18 WorkOrder_Part 3 12 2 14 Technician_Machine 3 12 2 14 total 25 100 14 114 facts the conceptual model states: 26 decisions the physical schema invents: 114 ratio: 4.3846 even if every decision has only two options, the number of physical schemas corresponding to the same logical model is: 20769187434139310514121985316880384 digit count: 35 ``` **System:** the conceptual model states 26 facts. **Notation:** the physical schema carries 7 tables and 25 fields. **Cost:** writing this schema requires giving **114 decisions**, and none of them is written in the conceptual model. The ratio is **4.3846**, meaning roughly **4.4 times** as many decisions as the model states are given outside the model. The last line is this number's counterpart in the notation's own language. If a decision offers at least two options — nullable or not, yes or no — the number of physical schemas corresponding to the same logical model is **two to the power of one hundred fourteen**, a thirty-five-digit number. The real number of options is larger than this, because type and length take more than two values. **The logical model fits all of this set of schemas at once** and prefers none of them over another. - **VM16** — The lower-bound count assumes every decision has only two options; the real number of options varies by storage product and is therefore not written. ## Who Owns the Decision The one hundred fourteen decisions do not disappear; only **their owner changes**. A decision has three possible owners. The model may have stated it as a fact. The decision may be **derivable** from another fact. Or a person, while writing code or building the schema, answers according to their own knowledge. The distinction between these three is measurable, and the measurement shows two separate behaviors. - **VM17** — A decision's owner is one of three: a fact written in the model, a result derived from another fact, or a person. When counted as a model fact, the conceptual model's fact count grows; when counted as a derived decision, it does not. ```python """Who owns the 114 decisions: carrying to the model and deriving from a rule are separate jobs.""" FIELD_DECISION = ["type", "length", "nullability", "default value"] TABLE_DECISION = ["primary key index", "character set"] LOGICAL = { "WorkOrder": ["key", "number", "opened", "status", "Technician_key"], "Technician": ["key", "name", "specialty"], "Part": ["key", "code", "name", "unit"], "Machine": ["key", "code", "type"], "Document": ["key", "type", "date", "WorkOrder_key"], "WorkOrder_Part": ["key", "WorkOrder_key", "Part_key"], "Technician_Machine": ["key", "Technician_key", "Machine_key"], } ATTRIBUTES = {"WorkOrder": ["number", "opened", "status"], "Technician": ["name", "specialty"], "Part": ["code", "name", "unit"], "Machine": ["code", "type"], "Document": ["type", "date"]} DECISIONS = [(t, a, k) for t, fields in LOGICAL.items() for a in fields for k in FIELD_DECISION] DECISIONS += [(t, None, k) for t in LOGICAL for k in TABLE_DECISION] def owner(decision, settings): t, a, k = decision is_conceptual = a in ATTRIBUTES.get(t, []) if settings["field"] and is_conceptual and k in ("type", "length"): return "model" if settings["requiredness"] and is_conceptual and k == "nullability": return "model" if settings["foreign"] and a is not None and a.endswith("_key") \ and k in ("type", "length"): return "derived" if settings["integrity"] and a == "key" and k == "nullability": return "derived" return "person" SETTINGS = [ ("conceptual model as is", dict(field=0, requiredness=0, foreign=0, integrity=0)), ("+ field for 12 attributes", dict(field=1, requiredness=0, foreign=0, integrity=0)), ("+ requiredness for 12 attributes", dict(field=1, requiredness=1, foreign=0, integrity=0)), ("+ foreign key derivation", dict(field=1, requiredness=1, foreign=1, integrity=0)), ("+ entity integrity", dict(field=1, requiredness=1, foreign=1, integrity=1)), ] print("total decisions:", len(DECISIONS)) print() print("configuration in model derived person model facts total") for name, settings in SETTINGS: tally = {"model": 0, "derived": 0, "person": 0} for d in DECISIONS: tally[owner(d, settings)] += 1 facts = 26 + tally["model"] print(f"{name:34s} {tally['model']:7d} {tally['derived']:10d} {tally['person']:4d}" f" {facts:12d} {facts + tally['person']:6d}") ``` ``` total decisions: 114 configuration in model derived person model facts total conceptual model as is 0 0 114 26 140 + field for 12 attributes 24 0 90 50 140 + requiredness for 12 attributes 36 0 78 62 140 + foreign key derivation 36 12 66 62 128 + entity integrity 36 19 59 62 121 ``` The table's last column is this lesson's most instructive number. In the first three rows the total stands **fixed at 140**: the facts the model carries and the decisions a person makes trade against each other **one for one**. Adding the type and length information of the twelve attributes to the conceptual model raises the fact count from 26 to 50 and lowers the decisions left to a person from 114 to 90. When requiredness information is added as well, facts reach 62, and the decisions left to a person reach 78. **Enriching the model does not reduce the number of decisions, it changes who owns the decision** — and this is a good trade, because a decision in the model is written, readable, and discussable. The last two rows do something different, and they **lower** the total. The type and length of a foreign key field can be **derived** from the primary key it refers to; and further, the field forming a primary key cannot be null — this is a direct consequence of the **entity integrity** established in the Data Modeling and Relational Theory course. With these two derivations, the decisions left to a person drop from 78 to **59**, and the total drops from 140 to **121**. Derivation is different from trading: it genuinely reduces the number of decisions without enlarging the model, because it makes stating the same information twice unnecessary. ## The Model's Silence Makes a Decision Invisible Each of the remaining fifty-nine decisions is a person's answer, and this answer leaves no trace in the model. The consequence of this is not that the decision is wrong; it is that **wrongness cannot be shown.** Looking at a single field is enough. - **VM18** — The part code pool has eight codes, and only the first five had been seen at decision time. ```python """A single invented decision: the length of the Part.code field. The model rules out no option.""" CODE = ["MK-01", "MK-002", "TR-08", "PL-7", "GK-1", "RD-1140", "HD-220-A", "SB-33012"] print("length rejected codes which ones") for length in range(4, 11): rejected = [k for k in CODE if len(k) > length] print(f"{length:6d} {len(rejected):14d} {', '.join(rejected) if rejected else '-'}") print() print("facts the conceptual model states about this decision: 0") print("smallest safe length read from the data:", max(len(k) for k in CODE)) print() seen = CODE[:5] choice = max(len(k) for k in seen) later = [k for k in CODE if len(k) > choice] print("codes seen at decision time:", len(seen), "| chosen length:", choice) print("codes arriving later:", len(CODE) - len(seen), "| rejected:", len(later), "->", ", ".join(later)) ``` ``` length rejected codes which ones 4 6 MK-01, MK-002, TR-08, RD-1140, HD-220-A, SB-33012 5 4 MK-002, RD-1140, HD-220-A, SB-33012 6 3 RD-1140, HD-220-A, SB-33012 7 2 HD-220-A, SB-33012 8 0 - 9 0 - 10 0 - facts the conceptual model states about this decision: 0 smallest safe length read from the data: 8 codes seen at decision time: 5 | chosen length: 6 codes arriving later: 3 | rejected: 3 -> RD-1140, HD-220-A, SB-33012 ``` Of the one hundred fourteen decisions, this is just one: the length of the `Part.code` field. The conceptual model states **zero facts** about this decision; it states that the part code is unique, not how many characters it has. Because of this, every option between 4 and 10 is **equally suited** to the model, and none can be ruled out by looking at the model. The only thing that rules out a decision is data, and the data is incomplete at decision time. The person making the decision saw five codes, the longest being six characters, and chose a length of 6. All **three** of the codes that arrive later are rejected. This is not a mistake, it is a **guess** — and the fact that it is a guess is not written in the model, because this row does not exist in the model at all. Five years later, someone looking at that field cannot tell whether the number 6 is a domain rule or one day's guess. From this follows the data-modeling counterpart of the course's rule: **every place the model is silent is a place of decision, and the decision made there cannot be discussed, because it does not appear in the model.** The number one hundred fourteen is therefore not a measure of schema size but a measure of **invisibility**. ## Summary - The physical schema requires four decisions per field and two per table. Seven tables and twenty-five fields produce 100 field decisions and 14 table decisions, a total of **114 decisions**. - The conceptual model states 26 facts; the physical schema invents 114 decisions. The ratio is 4.3846, roughly 4.4 times what the model states. - Even if every decision has only two options, the number of physical schemas corresponding to the same logical model is 2 to the power of 114, a thirty-five- digit number; the logical model fits all of them at once. - Carrying a decision into the model is a **trade** and does not change the total: when field and requiredness information is added for 12 attributes, facts rise from 26 to 62 while the decisions left to a person fall from 114 to 78, and the total stays fixed at 140. - **Deriving** a decision from a rule genuinely lowers the total: when the foreign key's type comes from the key it refers to, and the key's non-nullability comes from entity integrity, the decisions left to a person become 59 and the total becomes 121. - A decision the model is silent about becomes invisible: the model states zero facts about the length of `Part.code`, the five codes seen at decision time lead to a chosen length of 6, and all three codes arriving later are rejected. ## Course Wrap-Up Ten lessons modeled a single maintenance shop and asked a single question: how many other systems can a notation not distinguish the system from. That was the course's rule — **a notation that does not state how many systems it cannot tell apart is counted as unmeasured.** In every lesson, three numbers stood side by side: the system's real fact count, the number of symbols the notation writes, and the cost between them. | Lesson | System | Notation | Cost | |---|---|---|---| | Notation Family and Loss | 5 associations, 11 hidden fields | 20 symbols | **2048** systems fall onto the same diagram | | Class Diagrams | the same 5 associations | 25 and 31 symbols | **64** systems remain at 25 symbols, **1** at 31 | | Component and Deployment Diagrams | 6 classes, **203** partitions; 540 covering assignments | 3 boxes + 2 links | the crudest drawing is consistent with **10 partitions**; link symbols reduce 540 to 20, content symbols to 1 | | Use Case Diagrams | 20 facts: 2 actors, 3 scenarios, 5 associations, 3 ownerships, 7 internal steps | 9 symbols | **512** systems fall onto the same diagram; baseline ambiguity 128 | | Sequence Diagrams | 24 traces | 1 diagram, 1 trace | coverage **1/24 = 0.0417**; four diagrams 0.1667 | | Activity Diagrams | 10 tasks, 13 prerequisites | 23 symbols, 1 partial order | **168 total orders** are accepted, a single order's coverage is 0.006; a decision node carries it to 492 | | State Machine Diagrams | 6 real sequences | 10 symbols (4 states, 6 transitions) | under-acceptance **2**, over-acceptance **2**; adding two transitions gives 0 and **26** — thirteenfold | | The Entity–Relationship Model | 26 facts | 26 written items | **1,048,576** systems fall onto the same notation | | Conceptual, Logical, and Physical Model | 26 facts | 7 tables, 25 fields | **2 dropped constraints**, 13 invented fields, 175 over-acceptances | | From Model to Schema | 26 facts | 7 tables, 25 fields | **114 invented decisions**, 4.4 times what the model states | A second reading of the table is more valuable than the first: **fixing a notation can make it more wrong.** Adding two transitions to the state machine zeroed out the rejected sequence but raised over-acceptance thirteenfold. Demoting a fact from entity to attribute simplified the model but made eleven states unrepresentable. Rescuing a dropped constraint required adding a field not present in the model at all. Adding detail is not always a gain, and it **is not added without counting the loss.** The third and most expensive result came from data modeling: **a level of abstraction both forgets and invents, and the two are counted separately.** On the way down from the conceptual model to the physical schema, two of the five constraints dropped, but 114 decisions appeared that never were written in the model. The two forgotten constraints were visible, because they had once been written in the model; the 114 invented decisions are invisible, because they were never written at all. **The most dangerous thing about a notation is not what it erases, but what it never states.** No diagram was drawn throughout the course. Every notation was built and counted as a list of the information items it carries; what was measured was not the picture's beauty, but **how many systems the picture collapsed onto one picture**. Which diagram type suits which question, and what it costs to keep a set of diagrams current, were not this course's questions; both were measured in the Software Architecture curriculum's Architectural Decisions and Documentation course and were not carried over here. The next course, **Advanced Algorithms and Problem Solving**, carries the same discipline forward on a different notation. There, reducing a problem to a recognized algorithmic pattern is taught — divide and conquer, greedy choice, dynamic programming, backtracking. Reduction is itself a choice of notation: fitting a problem into a pattern makes some of its aspects invisible, and if the pattern's conditions are not met, the answer comes out silently wrong. This is why a justification stands at the center of that course: **showing that** the optimal-substructure and overlapping-subproblem conditions **are satisfied**. The rule here holds there too — until what a reduction drops is written down, the reduction is counted as unmeasured.