From ER diagrams to tables
Logical design turns the conceptual model into relations with keys and constraints. Standard mapping rules make this systematic.
Strong entities
Each strong entity set becomes a table whose columns are its simple attributes, with its key as the primary key. Composite attributes are flattened into their components, such as street, city, and postcode columns. Derived attributes are usually not stored, but computed when needed, unless performance requires it.
Weak entities
A weak entity set becomes a table that includes the owner's primary key as a foreign key. Its primary key combines the owner's key with the weak entity's partial key, such as (isbn, copy_number) for library copies. Typically, deleting the owner cascades to its weak entities.
One-to-many relationships
For a one-to-many relationship, put a foreign key on the 'many' side referencing the 'one' side: each employee row stores its department_id. Relationship attributes move to the same table. If participation on the many side is total, the foreign key is declared NOT NULL.
Many-to-many relationships
A many-to-many relationship becomes its own linking table, also called a junction or associative table, containing foreign keys to both entities, typically forming a composite primary key, plus any relationship attributes: Enrolment(student_id, course_id, grade). Without a linking table, there is no correct way to store many-to-many data.
One-to-one relationships
For a one-to-one relationship, place a foreign key with a UNIQUE constraint in either table, usually the one with total participation, or merge the two tables if they are always used together. Separate tables suit optional or sensitive data, such as keeping medical details apart from basic staff records with tighter access control.
Multivalued attributes and subtypes
A multivalued attribute becomes a separate table, such as PhoneNumber(person_id, phone), with one row per value. Subtypes can be mapped in three ways: one table for the whole hierarchy with nullable subtype columns and a type column; one table per subtype including inherited columns; or a base table plus subtype tables sharing its key. Each trades simplicity, null values, and query joins differently.
Choosing types and constraints
Logical design also chooses column types: integers for counts and IDs, NUMERIC or DECIMAL for money (never floating point, which cannot represent 0.10 exactly), dates and timestamps with time zones for events, and appropriately sized text. CHECK constraints encode rules such as quantity > 0, and UNIQUE constraints enforce alternate keys like email.
Worked example
Mapping the library: Member(member_id PK, name, email UNIQUE); Book(isbn PK, title, year); Copy(isbn FK to Book, copy_number, PK (isbn, copy_number)); Author(author_id PK, name); Writes(author_id FK, isbn FK, PK (author_id, isbn)) for the many-to-many relationship; Loan(loan_id PK, member_id FK, isbn, copy_number, FK (isbn, copy_number) to Copy, borrowed_on, due_on, returned_on). A copy's current loan is the one with returned_on null.
Key takeaways
Strong entities become tables keyed by their key; composite attributes are flattened and derived ones usually computed. Weak entities include the owner's key in a composite primary key. One-to-many relationships add a foreign key on the many side; many-to-many relationships need a linking table with a composite key; one-to-one relationships use a unique foreign key or merged tables. Multivalued attributes get their own tables; subtypes map to single, per-subtype, or base-plus-subtype tables. Use exact numeric types for money and constraints for business rules.
Flashcard
Entity becomes a table keyed by its key
Check your understanding
How should a many-to-many relationship between Author and Book be stored?