Recap: primary and foreign keys

DDD2 introduced the primary key (the attribute that uniquely identifies a record, underlined on an ER diagram) and the foreign key (an attribute linking to another entity's primary key, marked with an asterisk). Every entity in the Craigmillar Community Sports Club database uses a single-attribute primary key so far: memberID, classCode, instructorRef, and bookingRef. This lesson introduces a third kind of key — one made from more than one attribute at once.

What is a compound key?

A compound key is a primary key made up of two or more attributes combined, used when no single attribute on its own is guaranteed to be unique, but the combination of two attributes together is. This typically happens in a resolving entity — a table that exists to link two other entities — where neither foreign key alone identifies a unique record, but the pair of them together might.

Worked example scenario — Student enrolment on Modules

To see a genuine compound key in action, this section uses a small, separate example: a college's StudentModule entity, recording which students are enrolled on which modules, where the college's rule is that a student can only enrol on a given module once. Neither studentID alone (one student enrols on many modules) nor moduleCode alone (one module has many students) is unique by itself — but the combination of a specific student and a specific module is unique, because the rule guarantees no repeats. That combination, studentID + moduleCode, is the compound key.

Why doesn't Booking use a compound key?

It's natural to ask why Booking — which also links two entities, Member and Class — doesn't use memberID + classCode as a compound key the same way. The difference is the underlying rule: a sports club member is generally allowed to book the same class more than once (rejoining after a cancellation, booking a repeating weekly slot across several separate transactions). That means the combination of memberID and classCode is not guaranteed to be unique for Booking, the way studentID + moduleCode is for StudentModule. This is exactly why Booking has its own single-attribute primary key, bookingRef, instead — a compound key only works when the combination itself is genuinely always unique, and here it isn't.

What is a data dictionary?

A data dictionary is a table that documents every attribute of every entity in a database, in one place, before any table is actually built. For each attribute it records: which entity it belongs to, its name, whether it's a primary key (PK) or foreign key (FK), its data type, and its field size. The Higher course requires a data dictionary covering three or more entities — the sports club's four-entity database comfortably meets this.

Choosing a data type

Every attribute needs one of five data types: text (names, codes, addresses — anything alphanumeric), number (values used in calculations, like capacity or feePerSession), date (calendar dates, like dateJoined), time (clock times, like startTime), or Boolean (true/false values, like qualified or attended). Choosing the right type matters for both storage and validation — DDD5 builds directly on the data dictionary's type column when it adds validation rules.

Choosing a field size

Text attributes need a maximum field size stated in characters — large enough for realistic data, but not wastefully large. postcode might be sized at 8 characters (covering the longest realistic UK postcode with a space), while surname might be sized at 25. Number, date, time, and Boolean attributes don't need a character-count field size the way text does, so their field size column is often left blank or marked "n/a".