Part 4 of 5 · 2 min read
Reading a schema diagram
What a box-and-line schema diagram claims about a database, read correctly.
Reading a schema diagram
Think of a simple floor plan. Each room is a box. A line between two boxes means a door connects them. A label on the line says what kind of door it is.
In plain words: a schema diagram draws each table as a box listing its columns. It usually marks the primary key. A line connects two tables wherever a foreign key links them. Reading the diagram means reading, for every line, which table holds the reference and which table it points at. The line is not symmetric, even when drawn with no arrowhead.
A worked example. customers (customer_id [PK], full_name, address) and orders (order_id [PK], customer_id [FK], placed_on) draw as two boxes. A line connects them. Reading it correctly means saying this: one row of orders points at exactly one row of customers, through customer_id. It does not work the other way. Nothing in customers points back at orders.
The key lines. The same two tables, written the way a diagram would describe them before any drawing tool gets involved:
customers (customer_id PK, full_name, address)
orders (order_id PK, customer_id FK -> customers.customer_id, placed_on)
Schema diagram, entity-relationship diagram
A schema diagram shows a database's actual tables, columns and foreign keys, after a design is finished. An entity-relationship diagram, covered in Level 3, is drawn earlier. It plans what the tables should be, before any table exists. The two look similar but belong to different stages of designing a database.
Example: the two-box diagram above is a schema diagram, since it was drawn directly from existing CREATE TABLE statements. A diagram drawn earlier, to decide whether orders needed its own table at all, would have been an entity-relationship diagram.
Quick notes
- A table is a fixed set of named columns plus its current rows; the schema is the structure, the instance is the current rows.
- "Relational" refers to the mathematical relation (a set of tuples), not to foreign-key relationships between tables.
- Relation, tuple and attribute are the formal names for table, row and column.
- A schema diagram shows tables, columns and foreign keys after a design is finished; an entity-relationship diagram plans them beforehand.
- A line between two tables in a diagram is not symmetric: one table's foreign key points at the other's primary key, not the reverse, unless a second, separate foreign key says so.