03 / 32 · 5 min
Entities, attributes, relationships
Before tables there is a drawing. The ER model is how you decide what the tables should be, and getting it wrong costs more than any amount of SQL can recover.
An entity is a thing you store facts about: a student, a course, a book. An attribute is one of those facts: a name, a credit count.
The hard question is always the same one: is this an entity or an attribute? Two tests, and either one being yes makes it an entity:
- 1Does it have facts of its own? A publisher with an address and a phone number is an entity. A book's page count is not.
- 2Is it shared? If forty books have the same publisher, storing the publisher's address on each book means forty copies of it.
A relationship connects entities, and its cardinality says how many:
| Kind | Example |
|---|---|
| One to one | A student has one hostel bed |
| One to many | A department has many students |
| Many to many | Students enrol in courses |
Many-to-many always becomes its own table. A student takes many courses and a course has many students, so enrolment exists: and the moment you ask "when did they enrol?", you have proof it had to.
Takeaway: if it has its own facts, or it is shared, it is an entity. If a relationship has its own facts, it is a table.