An equipment list and an equipment history answer different questions. The list says which item exists and where it belongs. The history says what happened to that item on particular dates. Keeping those subjects in separate linked tables makes repeated events easier to record without repeatedly typing the equipment description.
We will design Equipment and ServiceEvents for an entirely fictional resource store. There are eight original records across the two tables: three equipment items and five events. No real student, staff, financial or other personal records are used. The task is table design and relationship checking; SQL programming and full normalisation theory are outside the lesson.
Start with the meaning of one record
One Equipment record represents one physical item. One ServiceEvents record represents one event for one item. These definitions matter more than the visual appearance of a table. If one row tried to hold “first service”, “second service” and “third service”, what would happen at the fourth service?
Instead, add another event row each time. The equipment item keeps its stable identifier, while each event receives a separate identifier. This supports a one-to-many relationship: one equipment item may have several service events, but each event in this design concerns exactly one item.
Microsoft’s database-design guide explains subject-based tables and primary/foreign-key links. The following invented design applies those ideas to equipment only.
Worked case 1: choose fields and dependable keys
| Field | Data type | Rule and purpose |
|---|---|---|
| EquipmentID | Short text | Primary key; required, unique, nonblank and stable |
| EquipmentName | Short text | Required descriptive name; duplicates permitted |
| Location | Short text | Required location label; not an identifier |
| Field | Data type | Rule and purpose |
|---|---|---|
| EventID | Short text | Primary key; required, unique and nonblank |
| EquipmentID | Short text | Required foreign key matching Equipment.EquipmentID |
| ServiceDate | Date | Required valid date; displayed as YYYY-MM-DD |
| EventType | Short text | Required description of the event category |
These identifiers are text because their letters and hyphens are meaningful labels, not quantities for arithmetic. The linked EquipmentID fields use the same text format. Dates should be stored as dates in an application that provides a date type, even though we display them in an unambiguous year-month-day format.
A primary key identifies exactly one record in its table. In this design a key may not be missing, empty or only spaces, and duplicates are rejected. EquipmentName is unsuitable: two real physical items can share a description. A location is also unsuitable because an item can move and many items can occupy one room.
| EquipmentID | EquipmentName | Location |
|---|---|---|
| EQ-101 | Clamp lamp | Studio A |
| EQ-102 | Clamp lamp | Studio B |
| EQ-103 | Document camera | Studio A |
| EventID | EquipmentID | ServiceDate | EventType |
|---|---|---|---|
| SV-301 | EQ-101 | 2026-05-04 | Inspection |
| SV-302 | EQ-102 | 2026-05-04 | Inspection |
| SV-303 | EQ-101 | 2026-06-12 | Cable check |
| SV-304 | EQ-102 | 2026-06-15 | Label replacement |
| SV-305 | EQ-101 | 2026-07-02 | Inspection |
EQ-101 and EQ-102 deliberately share the name Clamp lamp. This is not a duplicate-record error: they are two different items with distinct identifiers. EQ-103 is a valid catalogue item even though it has no event yet. A primary key should remain stable when its item is moved or renamed.
Worked case 2: put the foreign key on the many side
ServiceEvents.EquipmentID links each event to Equipment.EquipmentID. The dot here separates a table name from its field name. In the parent table, EQ-101 appears once. In the event table, it appears three times because that item has three events.
Equipment.EquipmentID (one) → ServiceEvents.EquipmentID (zero or many). Each event must match one parent item. This native-text relationship statement contains the same information as the diagram below.
Do not make ServiceEvents.EquipmentID unique. Doing so would wrongly limit each equipment item to one event. It is EventID that distinguishes event rows. A repeated foreign key is expected here; a repeated EventID is not.
Count by identifier rather than by name. EQ-101 has three events, EQ-102 has two, and EQ-103 has zero. The sum, 3 + 2 + 0 = 5 events, agrees with the event-table row count. Counting all Clamp lamp events together would hide the difference between the two lamps.
Worked case 3: make integrity rules enforce the design
A relationship drawn on paper is a plan, not a protection mechanism. Configure both primary keys, require the event’s EquipmentID, and enforce referential integrity between the linked fields. Microsoft’s guide to table relationships describes how this prevents links to nonexistent parent records.
For this demonstration, deletion of an equipment item with existing events is restricted; automatic cascade deletion is off. Key changes that would break existing links are also restricted. This deliberately protects the event history instead of silently removing it when an equipment row is removed.
| Proposed operation | Expected result | Reason |
|---|---|---|
| Add another Equipment row with EQ-101 | Reject | Equipment primary key already exists |
| Add another event with EventID SV-301 | Reject | Event primary key already exists |
| Add SV-306 for nonexistent EQ-999 | Reject | Foreign key has no matching parent |
| Add SV-306 with a blank EquipmentID | Reject | Every event must identify its item |
| Add SV-306 for existing EQ-102 | Accept | Repeated foreign key is legitimate |
| Delete EQ-101 while its events remain | Reject | Restricted deletion preserves the links |
The accepted SV-306 is a test insertion only and is rolled back after checking, so the published example still has eight records. The rejected attempts must leave the dataset unchanged. The accompanying local validation uses enforced constraints and checks these outcomes, rather than merely scanning the sample for duplicates.
Not every logical error is prevented by a foreign key. If someone chooses EQ-102 when the event actually concerned EQ-101, the identifier still exists and the link passes. Key constraints protect structure; accurate evidence and careful entry are still necessary.
Worked case 4: read a combined result without duplicating the catalogue
A combined view can show the item name next to each event by matching equal EquipmentID values. It does not need to store a new copy of EquipmentName inside every event record. The following event-centred result contains exactly one row for each of the five events.
| EventID | EquipmentID | EquipmentName | ServiceDate | EventType |
|---|---|---|---|---|
| SV-301 | EQ-101 | Clamp lamp | 2026-05-04 | Inspection |
| SV-302 | EQ-102 | Clamp lamp | 2026-05-04 | Inspection |
| SV-303 | EQ-101 | Clamp lamp | 2026-06-12 | Cable check |
| SV-304 | EQ-102 | Clamp lamp | 2026-06-15 | Label replacement |
| SV-305 | EQ-101 | Clamp lamp | 2026-07-02 | Inspection |
EQ-103 is absent from this event-centred result because it has no event to display. That does not mean it disappeared from Equipment. If the question asks for every equipment item, including unserviced ones, the result must be designed to preserve those parent items and show an empty event side.
The two Clamp lamp descriptions in the catalogue lead to five visible event rows, but the link is still the ID. Matching only the description would incorrectly pair each lamp event with both lamps, multiplying rows and confusing locations.
Three solved practice tasks
Task 1: evaluate a proposed primary key
Question: A learner proposes Location as the Equipment primary key because the first two sample items were stored in different rooms. Is that dependable?
Solution: No. A small sample cannot establish a lasting uniqueness rule. Two items may share a room, as EQ-101 and EQ-103 already do, and an item can move. Use EquipmentID as the stable identifier and keep Location as descriptive data.
Task 2: explain a rejected event
Question: A new record has EventID SV-410 and EquipmentID EQ-450, but EQ-450 is absent from Equipment. Does a unique EventID make the record valid?
Solution: No. The unique event key meets one rule, but the foreign key fails. Check the equipment reference against the actual catalogue. Correct a mistyped ID or add a genuinely new equipment item through the appropriate catalogue process before recording its event. Do not invent an item merely to suppress an error.
Task 3: predict counts after a valid addition
Question: In a fresh copy of the original dataset, add SV-411 for EQ-103 on 2026-08-10 with event type Inspection. What are the new counts and matched-result length?
Solution: Equipment still has 3 rows; ServiceEvents has 6 rows. Counts by item become EQ-101: 3, EQ-102: 2, EQ-103: 1. The event-centred matched result has 6 rows, because each valid event matches exactly one unique parent.
Frequently asked questions
- Can two equipment records have the same name?
- Yes. Two separate items may both be called Clamp lamp. Their EquipmentID values must differ so their histories remain distinguishable.
- Can a foreign key repeat?
- Yes, on the many side of this relationship. Several service events may refer to the same equipment item, while each event still has its own unique EventID.
- Must every equipment item have a service event?
- No. This design permits an equipment item with zero events. Every event must refer to exactly one existing equipment item.
- Can the foreign key be blank here?
- No. ServiceEvents.EquipmentID is required in this design. Some other designs allow optional links, but that is not the rule for these event records.
- Does drawing a relationship enforce it?
- No. A diagram explains the design. The database must have actual key, required-field and referential-integrity constraints for invalid records to be rejected.
- Why not use the service date as the event key?
- Different items can be serviced on the same date, and one item could have more than one event that day. A date is useful information but is not guaranteed unique.
- Does this example need real learner records?
- No. It uses invented equipment and service events only. No names, admission numbers, fee records or other learner personal data are needed.
This equipment-only lesson uses original data and checked relationship outcomes. It is not an official marking scheme, a collection form for personal records or a claim that table design alone guarantees accurate information.
WhatsApp Us Now