Learn
A3.2.4 | CONSTRUCT TABLES FOR RELATIONAL DATABASES
01 | TURN THE MODEL INTO TABLE DEFINITIONS
The school club ERD identifies students, clubs and memberships. Now the design needs concrete tables: which columns exist, which values identify a row and which rules stop an invalid reference or duplicate membership?
To construct tables, supply the structure and its rules, not only a few example rows. A useful table specification names each column, gives a suitable type and identifies primary keys, foreign keys and other relevant constraints.
We will implement the Student–Club relationship through Enrolment, then test the definitions. The rule is one membership per student-club pair in this example; term-specific history would require a revised design.
The lesson also distinguishes a multi-column key from physically concatenating values into a string. Both ideas appear in discussions of combined keys, but they need careful handling.
02 | WRITE THE BUSINESS RULES FIRST
A student has a unique StudentID, a required name and an optional email. A club has a unique ClubID, a required name and a positive capacity. Students and clubs can exist before any memberships are recorded.
Each enrolment identifies exactly one existing student and one existing club. It has a required role and joining date. A student may join several different clubs, and a club may have several different students.
These requirements justify two parent tables and one linking table. Do not put a comma-separated list of clubs inside Student, or repeat the student’s contact details in every membership.
Capacity is a declared positive number here. The further rule “current members must not exceed capacity” compares records and needs suitable additional transactional logic; a simple positive-capacity check does not enforce it.
03 | CONSTRUCT THE STUDENT TABLE
| Column | Illustrative type | Key / constraint | Purpose |
|---|---|---|---|
| StudentID | VARCHAR(5) | PRIMARY KEY | Unique non-null student identifier |
| StudentName | VARCHAR(80) | NOT NULL | Required name; names may repeat |
| VARCHAR(120) | NULL permitted | Optional contact value |
The primary key identifies a specific row, not the first row on the screen. Two students can both be named Maya if their StudentIDs differ. The key must be unique and non-null.
The chosen character limits are illustrative requirements, not universal name or email limits. VARCHAR(5) does not alone mean exactly five characters or a particular ID pattern; add a format constraint if that is required.
NOT NULL requires a non-null value, but it does not universally reject an empty or whitespace-only text value. If a meaningful nonblank name is required, implement that additional rule too.
04 | CONSTRUCT THE CLUB TABLE
| Column | Illustrative type | Key / constraint | Purpose |
|---|---|---|---|
| ClubID | VARCHAR(5) | PRIMARY KEY | Club identification |
| ClubName | VARCHAR(80) | NOT NULL | Required descriptive name |
| Capacity | INTEGER | NOT NULL; CHECK (Capacity > 0) | A recorded positive whole-number capacity |
A type supplies one kind of rule and a constraint adds another. INTEGER represents whole numbers; the check requires a positive value. Neither rule makes ClubName unique unless that is separately required.
Keep one fact in its appropriate place. The club’s capacity belongs to Club, not independently in every student’s membership row. This reduces duplicated facts and conflicting updates.
A student count calculated from enrolments is a query result, not necessarily a separately stored attribute. If a derived count is stored for performance, it needs a consistency strategy.
05 | PLACE THE FOREIGN KEY ON THE MANY SIDE
In a one-to-many relationship, the child or many-side rows commonly carry the reference to their parent. Enrolment.StudentID references Student.StudentID, allowing several enrolments to identify the same student.
A foreign key must refer to an appropriate declared key under the DBMS’s rules. A primary key is the usual introductory target, but suitable unique keys can also be reference targets. Definitions must be compatible.
A foreign-key column does not automatically have to be unique. Making Enrolment.StudentID unique would incorrectly prevent a student joining multiple clubs in this model.
Because each enrolment needs a student, the reference is non-null here. Optional references in other designs may allow NULL under their rules; foreign keys do not universally make every reference mandatory.
06 | THE ENROLMENT TABLE HAS ONE COMPOSITE PRIMARY KEY
| Column | Illustrative type | Key / constraint |
|---|---|---|
| StudentID | VARCHAR(5) | PK part; non-null FK to Student |
| ClubID | VARCHAR(5) | PK part; non-null FK to Club |
| Role | VARCHAR(40) | NOT NULL; default Member |
| JoinedDate | DATE | NOT NULL |
The composite primary key is (StudentID, ClubID). StudentID alone repeats across a student’s different clubs; ClubID alone repeats across a club’s different students. Their combination must be unique.
Both key parts are also foreign keys, but those are separate rules. The primary key prevents duplicate membership pairs; the foreign keys require existing parent rows.
A Role belongs to the membership because one student can have different roles in different clubs. The joining date also describes that particular association.
07 | CHECK THE DESIGN WITH SAMPLE ROWS
| StudentID | PK | StudentName | |
|---|---|---|
| S01 | Maya | maya01@example.invalid |
| S02 | Leon | leon02@example.invalid |
| S03 | Maya | NULL |
| ClubID | PK | ClubName | Capacity |
|---|---|---|
| C01 | Robotics | 20 |
| C02 | Game Design | 16 |
| StudentID | PK part, FK | ClubID | PK part, FK | Role | JoinedDate |
|---|---|---|---|
| S01 | C01 | Builder | 2026-09-01 |
| S01 | C02 | Designer | 2026-09-02 |
| S02 | C01 | Programmer | 2026-09-01 |
The two Mayas are distinct students. Repeated S01 in Enrolment is valid because its ClubID differs. A second S01,C01 row would violate the pair’s uniqueness, even if its Role were different.
These rows illustrate the rules; they do not replace them. An empty Enrolment table should still have its primary and foreign-key definitions. NULL in the sample represents an absent email, not the literal text “NULL”.
08 | COMPOSITE AND CONCATENATED KEYS
A composite key uses multiple attributes as one identifier. Some teaching materials use concatenated key as another name for a combined key. Always make clear whether the design means multiple key columns or one string built by joining their values.
With a multi-column primary key, the DBMS treats StudentID and ClubID as a tuple or combination. It need not store a new text column containing both. This is the approach used in our main design.
If values are literally concatenated, an unambiguous encoding is necessary. Joining “1” and “23” without a separator yields “123”; joining “12” and “3” yields the same string even though the pairs differ.
A separator can help only if its use and component rules are controlled. Fixed-width or properly encoded components are other possibilities. A casual string join is not a substitute for well-defined uniqueness and reference constraints.
09 | SEE A CONCATENATION COLLISION
Compare two different identifier pairs using a naive concatenation and an explicit separator. The chosen components contain only digits, so the separator is unambiguous for these examples.
This activity illustrates representation only. It does not create a key or imply that arbitrary components can always be separated safely with any character.
10 | AN ALTERNATIVE SURROGATE IDENTIFIER
A design might give Enrolment its own generated EnrolmentID primary key. That can be useful when other records need to reference a membership through one column. A surrogate key is an introduced identifier rather than a direct meaningful domain combination.
However, a new primary key does not automatically prevent repeated student-club pairs. If the one-membership-per-pair rule still applies, add a suitable UNIQUE(StudentID, ClubID) constraint and the required non-null and reference rules.
A generated sequence value is not necessarily gap-free, and a displayed number alone does not explain the business rule. Identification and domain uniqueness need separate consideration.
For the current worked solution, keep the composite primary key. The alternative shows why different valid implementations must preserve the same intended constraints.
11 | IMPLEMENTATION EXAMPLE: CREATE THE THREE TABLES
The following SQL-style definitions express the worked design using features supported by many relational DBMSs. Exact syntax, enforcement and supported actions must be checked for the chosen system. This page displays the code; it does not connect to a database.
Read the parent tables first, then Enrolment. The two-column PRIMARY KEY is declared once at table level. The foreign-key clauses separately connect it to each parent.
CREATE TABLE Student (
StudentID VARCHAR(5) PRIMARY KEY,
StudentName VARCHAR(80) NOT NULL,
Email VARCHAR(120)
);
CREATE TABLE Club (
ClubID VARCHAR(5) PRIMARY KEY,
ClubName VARCHAR(80) NOT NULL,
Capacity INTEGER NOT NULL CHECK (Capacity > 0)
);
CREATE TABLE Enrolment (
StudentID VARCHAR(5) NOT NULL,
ClubID VARCHAR(5) NOT NULL,
Role VARCHAR(40) NOT NULL DEFAULT 'Member',
JoinedDate DATE NOT NULL,
PRIMARY KEY (StudentID, ClubID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
ON DELETE RESTRICT,
FOREIGN KEY (ClubID) REFERENCES Club(ClubID)
ON DELETE RESTRICT
);The example deliberately does not claim that VARCHAR length, mandatory text or a positive Capacity fully validates the domain. Additional format, nonblank, date and membership-capacity rules may be needed. Do not copy a partial specification and assume all business requirements are enforced.
12 | READ EACH CONSTRAINT AS A TEST
| Constraint | Question it answers | What it does not prove |
|---|---|---|
| PRIMARY KEY | Is this identifier unique and non-null? | The real person’s name is correct |
| FOREIGN KEY | Does this reference satisfy its declared parent-key rules? | The user is authorised or eligible to join |
| NOT NULL | Has a non-null value been supplied? | Text is necessarily nonblank or factually true |
| CHECK | Does this row obey the stated condition? | Every cross-row business rule is enforced |
| DEFAULT | What value is supplied under applicable omission rules? | Explicit NULL is always replaced or the value is appropriate in every case |
Well-defined tables improve integrity because the DBMS can reject inconsistent changes through these rules. They complement application checks, verification and permissions rather than replacing all of them.
In some systems a CHECK expression that evaluates to unknown can pass, so pair a range check with NOT NULL when a missing value is forbidden.
13 | TRY ADDING A MEMBERSHIP
Choose a student and club, then enter a nonblank role up to 40 characters for this activity. A successful proposed row is added to the in-page model, so trying the same pair again demonstrates the composite-key rule.
The activity explicitly rejects missing or unknown parents and duplicate pairs. It adds a nonblank role rule beyond the SQL example’s NOT NULL alone. The joining date is fixed for the teaching model; it is not a full database or a demonstration of all real DBMS checks.
Your rows last only while this page remains open. The activity does not enforce club capacity, eligibility or authorisation and does not send data anywhere.
14 | INSERT, UPDATE AND DELETE RELATIONSHIPS CAREFULLY
A child row needs valid parent references when the relevant constraint is checked. Creating Student and Club records before their Enrolment rows is the straightforward order in this example. Some DBMSs support deferred checks within transactions, but that is not assumed here.
Deleting a student who still has enrolments is restricted by the shown ON DELETE RESTRICT policy. This prevents leaving those references dangling. A system may choose cascading deletion or another valid policy for a different requirement, but it must be explicit and preserve intended history.
Updating a referenced identifier also needs a deliberate policy. Stable identifiers reduce avoidable changes; copying names into references does not solve the underlying identity problem.
A transaction can group related changes, and suitable concurrency control protects overlapping work. A table definition alone does not automatically solve every workflow race or cross-row capacity limit.
15 | ONE-TO-ONE AND ONE-TO-MANY TABLE DESIGNS
The same reference principle supports ordinary one-to-many relationships: Customer has a key and Order carries a non-unique customer reference. Add NOT NULL when every order must identify a customer.
For an appropriate one-to-one design, a child reference can also have a UNIQUE constraint, preventing several child rows referencing the same parent. Optionality still needs separate treatment, and the exact NULL/unique behaviour should be checked for the DBMS.
The rule “every parent must have at least one child” is not ordinarily enforced just by a foreign key in the child. It may need additional transactional, application or DBMS-supported mechanisms.
Construct tables from the stated rules, not from a memorised instruction that every foreign key must be unique or every relationship must use one identical template.
16 | TEST THE TABLES BEFORE TRUSTING THEM
Try valid and invalid cases: a new unique pair, a duplicate pair, an absent parent, a missing mandatory value and an out-of-range capacity. Check that valid work succeeds and the intended invalid work fails.
Also test assumptions the simple schema does not enforce. An empty name may need a nonblank rule; a full club needs a safe enrolment-capacity check; a real joining date may need a domain constraint. Distinguish a known gap from a constraint that merely looks reassuring.
Use a data dictionary to record each field’s meaning, type, permitted values and key/reference roles. Keep the ERD, definitions and application requirements consistent when the design changes.
Good integrity means meaningful rules are actually enforced and tested, not that the database can discover every real-world error without evidence.
17 | MATCH THE RULE TO THE CONSTRUCTION
Choose the mechanism most directly enforcing each stated requirement. A complete design needs several mechanisms working together.
18 | CONSTRUCT A COMPLETE TABLE ANSWER
For construct, provide the requested structure in a clear table specification, schema notation or suitable definition form. Name the tables and columns, give suitable types, label the complete primary key and show foreign-key targets explicitly.
For the club example, construct Student and Club with their own identifiers. Construct Enrolment with the pair as one primary key and each component as a reference. Include membership-specific attributes and suitable mandatory/range rules, and state the one-membership-per-pair assumption.
If you use the term concatenated key, clarify whether you mean a multi-attribute key or a physically combined representation. Avoid an ambiguous string that gives different pairs the same stored value.
Then test the construction with sample rows and edge cases. The next lesson examines normal forms and how dependencies influence which facts belong together.