Database Design Basics: Entities and Relationships

A database that grows without a plan tends to end up with the same fact typed in ten places and no clear answer to "which one is right?". A short design session before you create any tables prevents most of that. Design here means deciding what things you need to record, how they connect, and how each record will be identified.

This entry walks through that process step by step and applies it to a small music school. It assumes you know what tables and keys are, as covered in tables, rows, columns and primary keys. It names normalization but does not go deep into its formal rules.

Start with the questions

Design from the answers you will need, not from the data you happen to have. Write down the questions the database must answer in plain language. For the music school:

• Which lessons does each teacher give?
• Which students attend each lesson?
• Who is booked for guitar on Mondays?
• Which students have more than one lesson?

These questions reveal the things you must track and the links between them. If a question cannot be answered from your design, the design is missing something. If a piece of data answers no question at all, you may not need to store it.

Entities and attributes

An entity is a kind of thing you keep records about. Each entity usually becomes a table. An attribute is a fact about that thing, and it becomes a column.

Read your questions and underline the nouns. In the music school, the nouns are teacher, student and lesson. Each is a real thing with its own facts:

Give each entity a key that identifies one record and never changes. A made-up number works well, because names repeat and can change. Test each attribute with one question: does this fact describe only this entity? A teacher's name belongs in the teachers table. The name of the teacher does not belong in the students table.

Relationships: one-to-many and many-to-many

A relationship is how two entities connect. Count the connections from each side.

One-to-many

A teacher gives many lessons, but each lesson has exactly one teacher. This is one-to-many. You store it by putting a foreign key on the "many" side: the lessons table gets a teacher_id column that points at the teachers table.

Many-to-many

A student can attend many lessons, and a lesson can have many students, since group lessons exist. This is many-to-many. You cannot store it with a single column on either side, because one cell would have to hold a list. The solution is a third table, called a link table (also a junction or bridge table), with one row for each student-in-a-lesson pairing. Its primary key is the two columns together.

One-to-one, briefly

Sometimes one record matches at most one record in another table, such as a teacher and a private profile. These are rarer. They are often stored in one table unless there is a reason to separate them.

Worked example: a music school

Here is the design turned into tables. Create referenced tables first, so each foreign key points at something that exists.

CREATE TABLE teachers (
  teacher_id INTEGER PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL
);

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL
);

CREATE TABLE lessons (
  lesson_id  INTEGER PRIMARY KEY,
  teacher_id INTEGER NOT NULL REFERENCES teachers (teacher_id),
  instrument VARCHAR(30) NOT NULL,
  weekday    VARCHAR(9) NOT NULL
);

CREATE TABLE lesson_students (
  lesson_id  INTEGER NOT NULL REFERENCES lessons (lesson_id),
  student_id INTEGER NOT NULL REFERENCES students (student_id),
  PRIMARY KEY (lesson_id, student_id)
);

Now add a few rows. In the teachers table, 1 is Ruth Alvarez and 2 is Kenji Mori. In the students table, 10 is Lia Park, 11 is Omar Said and 12 is Beth Cole. The lessons and links are:

To answer "who is booked for guitar, and with whom?", join all four tables:

SELECT s.full_name AS student, l.weekday, t.full_name AS teacher
FROM lessons AS l
JOIN lesson_students AS ls ON ls.lesson_id = l.lesson_id
JOIN students AS s ON s.student_id = ls.student_id
JOIN teachers AS t ON t.teacher_id = l.teacher_id
WHERE l.instrument = 'guitar'
ORDER BY l.lesson_id, s.full_name;

Lessons 100 and 102 are the guitar lessons, both taught by Ruth. Lia attends both, and Omar attends only the Monday one. Lesson 101 is piano, so Beth is left out. If joins are new to you, SQL joins explained covers how they combine tables.

The foreign keys protect the design. PostgreSQL's tutorial describes the idea as referential integrity: a row cannot point at a teacher or student that does not exist. In PostgreSQL, deleting a teacher who still has lessons is refused by default (in SQLite, only once foreign key checks are switched on). That forces you to decide what should happen to those lessons.

A light word on normalization

Imagine the first draft of this school as a single sheet with columns lesson, teacher name, teacher phone, student name. Ruth's name and phone would appear on every row for every student in every lesson of hers. Changing her phone means editing many rows, and a missed row leaves two versions of the truth.

Normalization is the practice of arranging tables so each fact is stored once. It comes with formal levels called normal forms, usually numbered first, second and third. You do not need their exact definitions to design well. Two plain rules cover most of the benefit:

1. Each table describes one kind of thing.
2. Every column describes that thing, and nothing else.

When you see the same detail repeated across rows, or a cell holding a list, split it out into its own table and link with a key. Some designers repeat data on purpose, for speed in reporting, but that is a later decision made with open eyes, not a starting point.

Review checklist

Before you build, check the design against this list.

1. Does every table have a primary key?
2. Does every question on your list have a path through the tables?
3. Is every one-to-many relationship stored as a foreign key on the many side?
4. Is every many-to-many relationship stored in a link table?
5. Does each fact live in exactly one place?
6. Are columns that must always have a value marked NOT NULL?
7. Do column names say clearly what they hold?

Common mistakes

• Building tables before listing questions. You end up storing data nobody needs and missing data somebody does.
• Storing a list in one cell. A cell holding "Lia, Omar" cannot be searched, counted or linked correctly. Use a link table.
• Using names as keys. Names repeat and change. Use a separate ID.
• Copying details instead of linking. Copies drift apart. Store the detail once and refer to it.
• Skipping foreign keys. Without them, the database cannot stop records that point at nothing.
• Designing for every future possibility. Start with what you need now. Tables can be changed later, with care.

FAQ

Do I need a diagram?

A simple drawing of boxes and lines helps you see entities and relationships. Pen and paper work as well as any tool. The picture matters more than the software.

How many tables is normal?

It depends on the system. Small projects often have a handful. Count what the design requires, not a target number.

Can I change the design later?

Yes, and most databases do change. Adding a column is usually simple. Splitting a table once it holds data is harder, which is why a short planning step pays off.

Is normalization always right?

It is a good default for data that is changed often and must stay consistent. Reporting systems sometimes copy data on purpose to make reading faster. Know the rule before you bend it.

How is this different from designing for a document database?

Document databases often store related data together in one record instead of splitting it across tables. The trade-offs are covered in relational vs NoSQL databases. The full set of data topics is in the Data and Databases area.