Business Rules and Constraints
An E-R diagram records the structure of a domain but not the laws that govern it. This lesson separates business rules from database constraints, shows which rules the model can express and which it cannot, and builds the register that keeps them from evaporating between the interview and the schema.
Intermediate
Learning objectives
- Distinguish a business rule from a database constraint and explain why the difference matters
- Classify rules into the six families and predict where each one can be enforced
- Recognise the rules a conceptual model cannot express, such as maximums and state transitions
- Elicit missing rules using the eight question templates
- Produce a rule register with identity, source, affected elements and enforcement layer
- Detect contradictory and stale rules before they reach the schema
Prerequisites
- Finding Attributes and Relationships
- Glossary of Terms
Introduction
A finished E-R diagram of a library looks convincing. It has Book, Copy, Member, Loan, Branch. It has attributes with domains. It has cardinalities on every line.
And yet it cannot answer four of the most ordinary questions the librarian will ask:
- Can a member hold more than five loans at once?
- May a copy be lent to someone whose membership expired last month?
- Can a loan be renewed twice?
- Who is allowed to waive a fine?
None of these are in the diagram, and none of them are absent from the organisation. They are business rules: statements about what must or must not be true in the domain, which the data must respect. They live in the requirements text, in the staff handbook, in the head of the person who has done the job for eleven years — and, unless someone writes them down, they vanish between the interview and the schema.
The diagram records the structure of the domain. The rules record its laws. A schema without rules is a warehouse that accepts anything.
This lesson is about capturing those laws systematically, and about an equally important skill: knowing which of them the model can enforce and which it cannot, so that the rest are not silently lost.
What a business rule is
Business rule
A business rule is a statement, expressed in the language of the domain, that constrains the states the domain may legitimately be in or the transitions between them.
It is not a description of structure (that is the schema) and it is not a description of a feature (that is a functional requirement). It is a statement of the form "always", "never", "at most", "only if", "must be at least".
Examples from a library:
- A loan must always be associated with exactly one member and one copy.
- A copy may never be on loan to two members at the same time.
- A member may hold at most five open loans.
- A loan may be renewed at most twice.
- A fine may be waived only by a librarian.
Three properties of that definition deserve attention.
A rule is a statement about all states, not about one. "Member M-0031 currently has four loans" is a fact. "A member may hold at most five open loans" is a rule. Facts are data; rules are constraints on data.
A rule is expressed in the vocabulary of the domain. It says member, loan, copy — not row, table, foreign key. This matters because the same rule must survive translation into a relational schema and then into SQL, and each translation is a decision that can lose or distort it. If the rule is written in SQL from the start, there is nothing left to check the SQL against.
A rule has a source. Someone in the organisation asserts it. A rule with no source is a guess, and guesses should be labelled as such in the register rather than presented as requirements.
Rules versus constraints
The two words are used loosely and the looseness causes real damage, so let us fix the vocabulary for this course.
Constraint
A constraint is a rule that has been given a form some mechanism can check — a cardinality in the E-R model, a key or foreign key in the relational model, a CHECK, UNIQUE or NOT NULL clause in SQL, or a guard in application code.
Every constraint expresses a rule. Not every rule becomes a constraint.
So a rule is the intent; a constraint is an implementation of that intent at a particular layer. The gap between them is where most data quality problems are born, because a rule that becomes no constraint is a rule that nothing enforces.
The rest of this lesson is largely about managing that gap deliberately.
Six families of business rule
Rules come in kinds, and the kind predicts almost everything else: how to write it, which model element expresses it, and which layer can enforce it.
| # | Family | Typical wording | Library example | Model element that expresses it |
|---|---|---|---|---|
| 1 | Existence and participation | every … must have at least one … | Every loan belongs to exactly one member | Cardinality, minimum side |
| 2 | Cardinality and multiplicity | at most / at least N | A member holds at most 5 open loans | Cardinality if the bound is 1; otherwise not expressible |
| 3 | Identity and uniqueness | is identified by / is unique | A copy is identified by its barcode | Identifier in the E-R model; key in the relational model |
| 4 | Domain and value | must be one of / must be positive / between | A loan period is between 1 and 365 days | Attribute domain |
| 5 | Derivation | is computed from | A loan is overdue when today is past the due date | Derived attribute, or nothing |
| 6 | Transition, authorisation and process | may only be changed by / cannot move from X to Y | Only a librarian may waive a fine | Not expressible in the model |
Families 1 to 5 are, in whole or in part, expressible in a conceptual model. Family 6 is not, and pretending otherwise is one of the most common sources of false confidence in database design.
Let us take them in order.
Family 1 — Existence and participation
The simplest family, and the one that most often goes unstated because it seems obvious. Every loan has a member. Every copy belongs to a title. Every order has at least one line.
In the E-R model these become minimum cardinalities: a Loan participates obligatorily in belongs_to with Member. The minimum is 1, not 0.
The interesting cases are the ones where the minimum is genuinely 0: a member who has never borrowed anything, a book with no loans yet, a branch with no staff. Those minimums must be written down too, because a designer who assumes 1 everywhere creates a schema in which a newly hired librarian cannot be registered until they have made a loan.
Existence rules also cover the awkward ones:
- Every copy must belong to exactly one title — a
1minimum on one side only. - A loan must reference a copy that exists — a referential rule, and the reason foreign keys exist.
- No loan may exist without a member — the same as the first, stated negatively. Worth recording both phrasings, because stakeholders react to one and not the other.
Family 2 — Cardinality and multiplicity
Each order has one or more lines. A doctor may work in at most three departments. A student may enrol in up to eight courses.
Cardinality rules are the ones that most often break the model, because the E-R notation can express a maximum of 1 or N and nothing in between. There is no standard notation for at most 5.
When a bound is 1, the model expresses it: a maximum of 1 on the Member side of belongs_to makes Loan-to-Member many-to-one. When the bound is any other number, the model cannot express it, and the designer has three options:
- Record it in the register as a rule with no constraint and accept that it is enforced by the application, or by nothing.
- Encode it structurally if the number is small and fixed — three phone slots as three attributes, which works for three and collapses for thirty.
- Encode it as a checkable fact — a counter maintained by the application, verifiable by a query that the operations team runs.
Option 1 is usually right, provided the register exists. Option 2 is a trap beyond very small numbers. Option 3 is the professional answer when the rule is important enough to be monitored.
Family 3 — Identity and uniqueness
An ISBN identifies a title. A barcode identifies a copy. A member number identifies a member. Two members may not share an email address.
Identity rules choose identifiers, and this is the family where the conceptual model earns its keep: identifiers are first-class elements of the E-R model, and a badly chosen one is expensive to change later because it propagates into every foreign key.
Uniqueness rules that are not identity are subtler: no two members share an email constrains an attribute without making it the identifier. In the E-R model this is a uniqueness constraint on a non-key attribute; in the relational model a UNIQUE constraint; in the requirements register a rule that the identifier was not chosen to satisfy.
A third sub-family is uniqueness over a combination: a copy's barcode is unique within a branch versus unique across all branches. These are different rules, they produce different keys, and stakeholders frequently do not distinguish them until asked. Ask.
Family 4 — Domain and value
The loan period is between 1 and 365 days. The status is one of requested, approved, rejected. The fine is never negative. The return date is never before the loan date.
Domain rules attach to attributes and are the easiest family to enforce: CHECK, NOT NULL, enumerated types, foreign keys to a lookup table. The conceptual model records the domain in the attribute specification; the physical model turns it into a clause.
Two traps:
The domain is often a reference. Status must be one of four values looks like a domain rule and is really a reference to a small table. Whether to model it as an enumeration or as an entity depends on whether the values are managed by the organisation — and if the librarian wants to add a fifth status next year, the enumeration is wrong.
Cross-attribute rules are not domain rules. Return date is never before loan date involves two attributes of the same entity. It is still a value rule and it is still checkable, but it does not belong in either attribute's domain. In the register it gets its own entry, and in SQL it becomes a table-level CHECK.
Family 5 — Derivation
The total is the sum of the lines. The overdue days are today minus the due date. The number of open loans is the count of loans not yet returned.
Derivation rules say how a value is computed from other values. They are the family that most directly determines whether a redundancy exists in the schema, because a derived attribute is by definition redundant with its inputs.
The design question is never can this be derived but should it be stored:
| Situation | Store the derived value? | Why |
|---|---|---|
| Recomputed constantly, expensive, never disputed | Yes, as a cache | Recomputing on every read is the larger cost |
| Recomputed rarely, cheap | No | Storing creates a second source of truth for no gain |
| Must reflect the value at a moment in time | Yes, as a snapshot | A price at order time is not the current price |
| Auditable, legally significant | Yes, with the derivation recorded | The calculation must be reconstructible |
That table is the whole of the derived-attribute decision, and it belongs in the register next to the rule, because the next designer will otherwise "simplify" the schema by removing the snapshot and destroy the audit trail.
Family 6 — Transition, authorisation and process
Only a librarian may waive a fine. A loan cannot move from returned back to open. An order can only be cancelled before it ships. Two approvals are required above ten thousand euros.
No conceptual model expresses these. Not this one, not the relational model, not a CHECK constraint. They are rules about who may do what, in which order, under which conditions, and they live in application logic, in workflow engines, or in the head of the person who is the only one who knows.
This is not a failure of the model; it is a boundary. The professional move is to identify these rules explicitly, record them, and hand them to the layer that can enforce them — rather than to distort the schema trying to encode a state machine in a table, which produces columns like can_be_cancelled that are true or false depending on the caller.
A special case worth naming: state rules are half-expressible. The set of legal states is a domain rule (an attribute with an enumerated domain). The transitions between them are not. So a Loan.status column with domain {open, returned, overdue} is fine; the rule that a loan may go open → returned but never returned → open is not in the schema, no matter how the schema is written.
Where each family lands
The same rule has to survive four layers, and each layer can express a different subset. The table below is the map you will use for the rest of the course.
| Family | Conceptual model (E-R) | Relational model | DBMS (MySQL) | Application code |
|---|---|---|---|---|
| Existence / participation | Minimum cardinality | Foreign key + NOT NULL |
NOT NULL, FOREIGN KEY |
— |
| Cardinality (bound = 1) | Maximum cardinality | Unique constraint on the foreign key | UNIQUE |
— |
| Cardinality (bound > 1) | Not expressible | Not expressible | Trigger, or nothing | Counter check |
| Identity / uniqueness | Identifier, unique attribute | Primary key, UNIQUE |
PRIMARY KEY, UNIQUE |
— |
| Domain / value | Attribute domain | Domain, CHECK |
CHECK, type, ENUM |
Validation for UX |
| Cross-attribute value | Domain notes | CHECK |
Table-level CHECK |
— |
| Derivation | Derived attribute (marked) | Computed column or trigger | Generated column, view | Recompute or read |
| Transition / authorisation | Not expressible | Not expressible | Not expressible (in general) | Service layer, permissions |
Read the table downwards and a pattern appears: the further right you go, the less of the intent survives. The conceptual model holds the whole rule set as intent; the DBMS holds a subset as checkable constraints; the application holds what remains, if someone remembers to write it.
That is precisely why the register exists. It is the only artefact that contains the rules in their complete form after the diagram is finished.
What the model cannot enforce
Three specific impossibilities deserve to be memorised, because each is a place where designers routinely over-promise.
Maximums other than 1. The E-R notation has 1 and N. A maximum of 5 is a rule, not a cardinality. Some CASE tools let you annotate it; the annotation is documentation, not a constraint.
Minimums below 1 in the other direction. A minimum cardinality of 1 on Loan's side of belongs_to means every loan has a member. It does not mean every member has a loan — that would be a minimum of 1 on the Member side, and expressing it in a relational schema requires a deferred constraint or a trigger, because a member must exist before their first loan.
Anything about who. Roles, permissions, and authorisation are not modelled in E-R. An E-R diagram that grows a Role entity to capture "only librarians may waive fines" is modelling the wrong thing: the rule is about an action, not about a stored fact.
Eliciting rules: eight question templates
Rules rarely volunteer themselves. Stakeholders describe what the system does, not what it forbids, because the prohibitions are invisible to people who have internalised them. The fix is to ask the same eight questions about every entity and every association you have found.
| # | Template | What it surfaces | Library answer |
|---|---|---|---|
| 1 | How many of these can one of those have? | Cardinality and maximums | A member holds at most 5 open loans |
| 2 | Must every one of these have at least one of those? | Minimum cardinalities | Every copy belongs to exactly one title |
| 3 | What makes one of these different from another? | Identity and uniqueness | Barcode for copies, ISBN for titles |
| 4 | What values are possible, and which are forbidden? | Domains | Loan period 1–365 days; fine never negative |
| 5 | What must always be true, no matter what happens? | Invariants | A return date is never before the loan date |
| 6 | What can never happen, and what should the system do if it does? | Prohibitions and error handling | A copy cannot be on two open loans |
| 7 | In what order do things happen, and which orders are illegal? | State transitions | A loan goes open → returned, never back |
| 8 | Who is allowed to do this, and who is not? | Authorisation | Only a librarian waives a fine |
Question 6 is the highest-yield one in practice, and the reason is psychological: people remember exceptions. "What can never happen" invites the war story about the time the system allowed a book to be lent twice, and war stories come with precise, testable rules.
Question 7 is the one most often skipped and the one that produces the most application bugs, because a state machine that is not written down is re-invented, differently, by every developer who touches the code.
Reading rules out of prose
Requirements documents state rules in the middle of sentences, mixed with description. Four signals mark them:
- Quantifiers: all, every, each, no, none, only, at least, at most, exactly.
- Modals: must, shall, may, cannot, is required to, is forbidden to.
- Temporal words: before, after, within, no later than, until, as soon as.
- Conditionals: if, unless, provided that, in the case of, except when.
A sentence containing none of these is usually a description, not a rule. A sentence containing three is usually two rules glued together and should be split.
"Members must return items within the loan period unless a renewal has been granted, and at most two renewals are permitted."
That is three rules: a due-date rule, an exception for renewals, and a maximum on renewals. Split them; each needs its own register entry, its own enforcement decision, and its own test.
The rule register
The deliverable of this lesson is a table. It is unglamorous and it is the difference between a schema that works and a schema that merely compiles.
| Field | Purpose |
|---|---|
| ID | Stable identifier, BR-001. Never reused, never renumbered. |
| Statement | One rule, in domain language, in the form always / never / at most. |
| Family | One of the six, from the table earlier. |
| Source | Who asserted it: a person, a document, a regulation. |
| Affected elements | Which entities, attributes or associations it touches. |
| Enforcement | Model / relational / DBMS / application / none, with the reason. |
| Test | How a verifier would check that the rule holds. |
| Status | Confirmed, assumed, disputed, obsolete. |
Three fields do the heavy lifting.
Source turns a guess into a traceable claim. When the rule turns out to be wrong eighteen months later, the register says who to ask. When two stakeholders disagree, the register shows the disagreement instead of hiding it.
Enforcement is the field that prevents false confidence. Writing "application" next to a rule is an admission that the database will accept violations, which is exactly the information the next developer needs.
Test is the field that makes the rule real. If you cannot state how to check a rule, you do not yet understand it — and you will discover that while writing the field, not while debugging production.
Worked example: the library rule register
Here is the register for the Library Management System case study, after the interview. Twelve rules, drawn from the requirements text of the previous lessons plus the answers to the eight templates.
| ID | Statement | Family | Source | Affected elements | Enforcement | Test | Status |
|---|---|---|---|---|---|---|---|
| BR-001 | Every copy belongs to exactly one title | Existence | Head librarian | Copy, belongs_to |
Model: minimum 1, maximum 1 | SELECT COUNT(*) FROM copy WHERE title_id IS NULL = 0 |
Confirmed |
| BR-002 | Every loan is associated with exactly one member and one copy | Existence | Head librarian | Loan, Member, Copy |
Model: both minima 1; DBMS NOT NULL + FK |
Count of loans with null member or copy = 0 | Confirmed |
| BR-003 | A copy may not be on two open loans at the same time | Cardinality / prohibition | Incident in 2019 | Loan.status, Copy |
DBMS: partial unique index on (copy_id) WHERE status='open' |
Attempt to open a second loan for the same copy fails | Confirmed |
| BR-004 | A member may hold at most 5 open loans | Cardinality | Lending policy | Loan, Member |
Application: counter check at loan creation | Create a 6th open loan for a member and observe rejection | Confirmed |
| BR-005 | A loan may be renewed at most twice | Cardinality | Lending policy | Loan.renewals |
Application + DBMS CHECK (renewals BETWEEN 0 AND 2) |
Increment past 2 and observe rejection | Confirmed |
| BR-006 | A copy is identified by its barcode, unique across all branches | Identity | Catalogue manager | Copy.barcode |
Model: identifier; DBMS PRIMARY KEY or UNIQUE |
Insert duplicate barcode fails | Confirmed |
| BR-007 | A title is identified by its ISBN when present; otherwise by an internal catalogue number | Identity | Catalogue manager | Title |
Model: two alternative identifiers, one optional | Every title has at least one of the two | Confirmed |
| BR-008 | The loan period is 30 days, configurable per member category between 7 and 90 | Domain | Lending policy | LoanCategory.loan_period_days |
DBMS CHECK (loan_period_days BETWEEN 7 AND 90) |
Update to 100 fails | Confirmed |
| BR-009 | The return date is never before the loan date | Domain (cross-attribute) | Obvious | Loan |
DBMS table-level CHECK |
Insert reversed dates fails | Confirmed |
| BR-010 | A loan is overdue when today is past the due date and it has not been returned | Derivation | Head librarian | Loan.due_date, Loan.return_date |
Derived — not stored; computed in queries | Compare computed overdue set with a manual sample | Confirmed |
| BR-011 | A fine is calculated as 0.50 per overdue day, capped at the copy's replacement value | Derivation | Lending policy | Fine, Copy.replacement_value |
Application computes, stores as snapshot with the rate used | Recompute a historical fine with the rate recorded on it | Confirmed |
| BR-012 | A fine may be waived only by a librarian, and the waiver must record who and why | Transition / authorisation | Library director | Fine, Staff |
Application only — not expressible in schema | Attempt a waiver as a member role and observe rejection | Confirmed |
Reading the register
Five rules landed in the model (BR-001, 002, 006, 007, 008): minima, maxima of one, identifiers, domains. These are the ones the diagram will carry forward, and the ones a restructuring step must not accidentally break.
Two landed in the DBMS but not the model (BR-003, 009). BR-003 is instructive: a copy may not be on two open loans is a prohibition that a conceptual model cannot state, because the model has no notion of a loan's status. It becomes a partial unique index — a MySQL feature worth knowing, because it is the cleanest way to enforce "at most one open X per Y" without a trigger. BR-009 involves two attributes and therefore a table-level CHECK.
Three landed in the application (BR-004, 005, 012) and one in both (BR-005 has a CHECK for the plausible range and application logic for the real rule). BR-004 is the honest case: a maximum of 5 cannot be a constraint, so the register says so plainly rather than pretending.
Two are derivations with opposite decisions (BR-010, 011) and the contrast is the point. Overdue status is recomputed on demand — it depends on today, so any stored value would be wrong tomorrow. A fine is stored as a snapshot — it depends on the rate in force at the time, so recomputing it later with a new rate would silently rewrite history. Same family, opposite storage decision, both defensible, both recorded.
One rule was missing and the templates found it. BR-005 (renewals) never appeared in the requirements text. It surfaced under template 1 — how many times can this happen? — which is the most reliable of the eight for finding rules nobody thought to state.
Contradictions, conflicts and stale rules
A register with thirty entries will contain at least one contradiction. Finding them is a structured activity, not a matter of reading carefully.
Check 1 — two rules with incompatible bounds. A member may hold at most 5 open loans and a loan may cover up to 3 copies are compatible. A copy may be on at most one open loan and a loan may cover several copies are not, unless the loan is to a single member and the rule is restated per copy. Restate; do not average.
Check 2 — a rule that contradicts a cardinality already in the diagram. If the diagram says a member has at most one address and a rule says a member may register a winter and a summer address, one of them is wrong. The diagram is usually the one to change, but not always — sometimes the rule is the outdated one.
Check 3 — a rule that no element can carry. Each department must have at least one manager cannot be enforced if manages is optional on both sides. Either make the participation obligatory (and accept that a department cannot be created before its manager) or record the rule as application-enforced. Do not leave it as an unmarked gap.
Check 4 — a rule that was true and no longer is. Loans are recorded on paper and entered weekly was a rule in 2015. Registers accumulate these. Mark them obsolete with a date rather than deleting them, and check the dates against the requirements version.
Rules and time
A rule has a validity period, and schemas that ignore this age badly. Three patterns recur.
The rule changed and the data did not. The loan period was 30 days until 2022 and 21 days since. A loan_period_days on the member category records today's value; a due_date computed at loan creation records the value in force then. The second is the one that is correct after the change, which is why BR-008 stores the period on the category but the loan stores its own due date.
The rule will change and the data must anticipate it. The fine is 0.50 per day. If the rate changes, historical fines must not change. The register entry (BR-011) says store the rate used on the fine, which is a schema decision derived directly from a rule-about-time.
The rule applies only to part of the data. Copies acquired before 2020 have no barcode. Rules with temporal scope produce optional attributes and partial constraints, and stakeholders describe them as absolute until asked about the past.
The general principle: when a rule can change, store the value the rule produced, not a pointer to the rule. The exception is when the organisation genuinely wants retroactive recomputation, which is rare and should be confirmed explicitly.
Common mistakes
| Mistake | Symptom | Consequence | Fix |
|---|---|---|---|
| Treating rules as an appendix | Register written after the schema is frozen | Rules that change the design are discovered too late | Elicit with the eight templates during entity discovery |
| Writing rules in SQL | Register entries read CHECK (x > 0) |
Nothing left to validate the SQL against; intent lost | Write the rule in domain language; record the SQL separately |
| Assuming the DBMS enforces everything | "The database guarantees it" | Maximums, transitions and permissions silently unenforced | Classify by family; fill the Enforcement field honestly |
| Recording only the rules stakeholders volunteer | Register has 6 entries for a 40-entity domain | The invisible rules — the ones people have internalised — are missing | Ask all eight templates for every entity |
| No source field | Nobody knows who to ask | Disputed rules cannot be resolved; wrong rules persist | One source per entry, by name |
| No test field | Rules that cannot be checked | You do not understand the rule yet | If you cannot state the test, rewrite the rule |
| Deleting obsolete rules | History lost | The same rule is re-proposed and re-litigated | Mark obsolete with date and reason |
| Encoding authorisation in the schema | Role and Permission entities in an E-R diagram |
The model now describes an implementation, not the domain | Move to the application layer; keep the rule in the register |
| Treating a derived value as free | Stored totals everywhere | Two sources of truth; they diverge | Decide per rule using the derivation table |
| Averaging contradictions | Two incompatible maximums merged into a compromise | Neither rule holds; nobody knows what is enforced | Restate; escalate; decide |
Summary
- A business rule states what must or must not be true in the domain, in the domain's own vocabulary, with a named source. A constraint is a rule given a form some mechanism can check.
- The six families are existence, cardinality, identity, domain, derivation, and transition/authorisation. The family predicts where the rule can be enforced.
- The E-R model expresses minima, maxima of one, identifiers and domains. It cannot express maximums other than one, state transitions, permissions, or cross-row rules that are not key constraints.
- Four layers enforce different subsets: conceptual model, relational model, DBMS, application. The further right, the less intent survives — which is why the register is the only complete record.
- Elicit rules with the eight templates applied to every entity and association. Question 6 (what can never happen) and question 7 (in which order) have the highest yield.
- Read rules out of prose by their quantifiers, modals, temporal words and conditionals; split sentences that contain several.
- The register has eight fields; Source, Enforcement and Test are the ones that make it useful.
- Contradictions are found by four structured checks, not by careful reading.
- When a rule can change over time, store the value the rule produced rather than a pointer to the rule.
Verification questions
1. A stakeholder says: "Each student may enrol in at most eight courses." Which family is this rule, and where can it be enforced?
Show the solution
It is a cardinality rule with a maximum greater than one.
The conceptual model cannot express it: the E-R notation offers 1 and N, and 8 is neither. The relational model cannot express it either — no key or uniqueness constraint counts rows. MySQL cannot express it with a CHECK, because CHECK constraints in MySQL may not reference other rows or use subqueries.
So it is enforced in application code, at the point of enrolment, by counting the student's current enrolments inside the same transaction that inserts the new one. The register records Enforcement: application, and the Test field says: create a ninth enrolment for a student with eight and observe rejection.
Two refinements worth noting. First, the check must be inside the transaction and the isolation level must be strong enough that two concurrent enrolments cannot both see seven and both succeed — this is a real race condition, not a theoretical one. Second, if the rule is important enough to monitor, add a periodic verification query that reports any student with more than eight, so that a bug in the application does not go unnoticed.
2. Why is "a loan cannot move from returned back to open" not expressible in an E-R diagram, and what is the practical consequence?
Show the solution
Because the E-R model describes the states the domain may be in, not the transitions between them. Loan.status with a domain of {open, returned} records that those two values are legal. It says nothing about which changes are legal, and the notation has no construct for edges between attribute values.
The practical consequence is that the rule must be enforced where transitions happen: in the service layer that processes a return, and in any other code path that writes status. That in turn means:
- The rule must be written down, or each developer will implement their own version.
- Every write path must be identified — including bulk updates, administrative tools and data migrations, which is where such rules usually leak.
- A database-level defence is worth considering if the stakes are high: a trigger that raises an error on an illegal transition. It is not elegant, and it is more robust than an application convention that one script can bypass.
3. A derived attribute — the count of open loans per member — appears in the requirements. Should it be stored?
Show the solution
Apply the derivation table rather than answering from instinct.
Is it expensive to recompute? Counting loans for one member is cheap; counting for a member list page is a join and an aggregate per row, which is more expensive but still modest.
Must it reflect a moment in time? No — the count is now, always now. A stored value is wrong the instant a loan is opened or closed, so storing it buys nothing and costs consistency.
Is it auditable? The historical count is recoverable from the loans themselves, which are never deleted.
Verdict: do not store it. Compute it in the query, or expose it through a view if it is used in several places. If profiling later shows a real cost, add a cached counter maintained by the application — and record in the register that the cache exists, how it is updated, and what to do when it disagrees with the truth.
The answer would be the opposite for a fine (a snapshot of a historical calculation) or a price at order time. Same family, different decision, because the questions in the table have different answers.
Exercise
Rules for a gym membership system
A gym is replacing its spreadsheet. The interview produced this description. Extract every business rule you can, classify it, and decide where it can be enforced.
The gym has around 1,400 members. Each member has a membership number printed on their card and a tax code; the tax code is what the accountant uses, but the card number is what the turnstile reads. Members can be ordinary, students, or over-65, and the category determines the monthly fee. A member may register at one home branch and may also use any other branch.
Members book classes. Each class has a name, a weekly timetable slot, an instructor and a maximum number of participants. A member may book at most two classes per day and may not book the same class twice on the same day. Bookings may be cancelled up to two hours before the class starts; after that the member is marked as a no-show, and three no-shows in a month suspend booking rights until the end of the following month.
Instructors may teach several classes and may work at more than one branch. Every class must have exactly one instructor. Payments are recorded monthly; a member whose payment is more than 30 days late may not enter the gym.
The gym also rents lockers. A locker belongs to one branch and may be rented to one member at a time; a member may rent at most one locker.
Produce:
- A rule register with at least ten entries, using the eight fields.
- For each rule, the family and the enforcement layer, with a one-line justification.
- A list of the rules that cannot be expressed in an E-R diagram, and what you would do about each.
- Any contradictions or gaps you had to resolve by assumption, marked as such.
Commented solution
The register
| ID | Statement | Family | Source | Affected | Enforcement | Test | Status |
|---|---|---|---|---|---|---|---|
| BR-001 | A member is identified by the card number; the tax code is also unique | Identity | Interview | Member.card_no, Member.tax_code |
Model: identifier + unique attribute; DBMS UNIQUE |
Duplicate card or tax code insert fails | Confirmed |
| BR-002 | Every member has exactly one category, which determines the fee | Existence + domain | Interview | Member, Category |
Model: minimum 1; DBMS FK NOT NULL |
Member with no category cannot exist | Confirmed |
| BR-003 | A member has exactly one home branch and may use any other branch | Existence + cardinality | Interview | Member, Branch, uses |
Model: home_branch minimum 1 maximum 1; uses N:M |
Home branch null count = 0 | Confirmed |
| BR-004 | Every class has exactly one instructor | Existence | Interview | Class, teaches |
Model: minimum 1, maximum 1 on instructor side | Class with no instructor cannot exist | Confirmed |
| BR-005 | An instructor may teach several classes and work at several branches | Cardinality | Interview | Instructor, Branch |
Model: N:M | — | Confirmed |
| BR-006 | A class has a maximum number of participants | Domain | Interview | Class.max_participants |
DBMS CHECK (max_participants > 0) |
— | Confirmed |
| BR-007 | A member may book at most two classes per day | Cardinality > 1 | Interview | Booking |
Application — not expressible | Third booking same day rejected | Confirmed |
| BR-008 | A member may not book the same class twice on the same day | Uniqueness (conditional) | Interview | Booking |
DBMS: unique on (member_id, class_id, session_date) |
Duplicate booking insert fails | Confirmed |
| BR-009 | A booking may be cancelled up to 2 hours before the class starts | Transition | Interview | Booking.status, Class.start_time |
Application only | Cancel 1 hour before → rejected | Confirmed |
| BR-010 | Three no-shows in a month suspend booking rights until the end of the following month | Derivation + transition | Interview | Booking.status, Member |
Application only, with a stored suspended_until |
Mark third no-show and observe suspension | Confirmed |
| BR-011 | A payment more than 30 days late blocks entry | Derivation + transition | Interview | Payment, Member |
Application computes; turnstile reads the flag | Member with 31-day-old unpaid invoice refused | Confirmed |
| BR-012 | A locker belongs to one branch and is rented to at most one member at a time | Existence + cardinality | Interview | Locker, Branch, Rental |
Model: branch max 1; DBMS partial unique index on locker_id WHERE active |
Second active rental for same locker fails | Confirmed |
| BR-013 | A member may rent at most one locker | Cardinality (bound 1) | Interview | Rental, Member |
Model: maximum 1 on the member side of an active rental; partial unique index | Second active rental for same member fails | Confirmed |
| BR-014 | A monthly fee is recorded per member per month, with the amount in force at that time | Derivation + time | Interview | Payment |
Snapshot: store the amount, not a pointer to the fee table | Recompute a historical fee from the stored amount | Confirmed |
| BR-015 | A cancelled booking within the 2-hour window still counts as attended | Ambiguity — assumption | Designer | Booking |
Application | — | Assumed |
What cannot be expressed in the E-R model
- BR-007, BR-009, BR-010, BR-011 are transition or counting rules. None is expressible in the diagram. Each needs an explicit application-layer implementation, and BR-010 and BR-011 need a stored flag (
suspended_until,entry_blocked) because the turnstile must read a decision without recomputing it. That flag is a cache of a derivation, and the register must say so — otherwise the next designer will remove it as redundant and the turnstile will stop working. - BR-008 looks like a plain uniqueness rule but is conditional on the day: the same member may book the same class next week. That makes the uniqueness span three columns including a date derived from the class timetable. In the model this is a uniqueness constraint over a combination; in MySQL a
UNIQUEover three columns. Note the trap: if the class has several weekly slots,session_datemust be a real attribute of the booking, not a computed value, or the constraint cannot be declared. - BR-013 is the interesting one. At most one locker per member is a cardinality with a bound of 1, so it is expressible — but only because
Rentalis the association and the constraint is on the active rental. Modelled as a plain N:M association, it is expressible. Modelled with aRentalentity that keeps history (which it must, to bill past rentals), the constraint applies only to the open row, so it becomes a partial unique index. Two correct designs, one of which needs a DBMS feature.
Assumptions and gaps to resolve with the client
- Does a cancelled booking count as a no-show? The text says a no-show is a booking not cancelled in time; a member who cancelled 30 minutes before is not a no-show, but they also did not attend. Marked BR-015 as assumed: cancelled bookings do not count. This is a question for the gym manager, not a design decision.
- Does "three no-shows in a month" mean a calendar month or any rolling 30 days? Materially different, and not stated. Record as a gap.
- Is the tax code mandatory for all members? The text implies it, but guest day-pass holders may not have one. If they do not,
tax_codeis optional and the uniqueness constraint must tolerate nulls. - Can an instructor be a member? Not stated. If yes,
InstructorandMemberare two roles of onePerson, and the design changes substantially — the same recursive-role lesson as in the previous lesson. - What happens to a booking when a class is cancelled by the gym? Not stated; affects whether
Booking.statushas a fourth value.
The lesson to carry forward
Count the enforcement column: of fifteen rules, five are enforced by the model, four by the DBMS, and six by the application or not at all. A schema generated from this diagram would enforce a third of the gym's actual rules.
That ratio is normal. It is also invisible unless someone writes the register — which is the whole point of this lesson.
Where to go next
- The vocabulary the rules are written in: Glossary of Terms.
- The attributes and associations these rules constrain: Finding Attributes and Relationships.
- What to do when the requirements are silent or contradictory: Ambiguity and Incomplete Requirements.
- Where conceptual constraints become SQL: Introduction to Databases.
- A diagram whose cardinalities are themselves rules: Student and Course.