The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Relational model and design

ER modeling

How do you turn the relationships between entities into tables?

You turn three relationships into tables: members and posts (one-to-many), posts and tags (many-to-many), and members and profiles (one-to-one). For each relationship, a common design is placed next to a design that looks plausible but goes wrong. Give both the same job (finding what is connected, or connecting one more), and the query results differ.

Checked against real execution · The SQL of all 14 combinations was run as it is on MySQL 8.4.11, and the result of the write statement, all the rows of the query result, and all the rows of the tables afterwards were checked

Concepts in this lab

Entity-relationship model (entities, attributes, relationships)

This is a design method in which you organize the data to be stored into entities, attributes, and relationships, and show them in a diagram. An entity is something you store as a separate thing, such as a member or a post. An attribute is a value that an entity has, such as a name or a title. A relationship is a connection between entities, such as "a member writes a post". You use it in the step before creating tables, when you decide what to store and which things are connected. When you turn the model into tables, an entity becomes a table, an attribute becomes a column, and a relationship becomes a foreign key or an associative table. This diagram is called an entity-relationship diagram.

Types of relationships (one-to-one, one-to-many, many-to-many)

These are the kinds of relationships, divided by how many of one entity can be connected to how many of the other. One-to-many is a relationship in which one thing on one side connects to many on the other, such as one member writing many posts, and you put the foreign key in the table on the "many" side. Many-to-many is a relationship in which both sides connect to many, such as a post having many tags and a tag being attached to many posts, and you put an associative table in the middle. One-to-one is a relationship in which each side connects to only one, and you express it by adding a unique constraint to a foreign key. The reason for these rules is that a single cell can hold only one value.

Associative table

This is a table that you put between two tables when you turn a many-to-many relationship into tables. It has two foreign keys that point to the primary keys of the two tables, and one row means one connection, such as "this tag is attached to this post". Usually the two foreign keys are combined into the primary key, so that the same connection cannot go in twice. When connections increase, only the rows increase, and the shape of the table does not change. If a value that belongs to the connection itself appears, such as the date it was connected or its order, you add it to this table as a column. It is also called a junction table.

Primary key, unique key, foreign key

A primary key (PK) is the column that uniquely identifies each row of a table. Its values cannot be duplicated or empty, and a table has only one. A unique key is a unique constraint, which prevents duplicate values, and a table can have several. A foreign key (FK) is a column that references the primary key (or a unique key) of another table. It guarantees that the referenced row really exists.

Try these

• Default settings — Finds the java tag in the comma-separated string. The javascript post comes back too.
• Finding with an associative table — Exactly the 2 posts with the java tag come back.
• A fourth tag in tag1~tag3 — There is no column to put it in, so an error occurs.
• One more tag in the associative table — Inserting one row into the associative table is all it takes.
• Foreign key on the "one" side — Of the 2 posts that the member wrote, only 1 is found.
• One-to-one without a unique constraint — Two profiles go in for one member.
• One-to-one with a unique constraint — The second profile is blocked with error 1062.

Running with the default settings

1. CREATE TABLE, INSERT: This stores the many-to-many relationship between posts and tags without an associative table, by joining the tag names with commas in the tags column of the post table. To the DB, tags is just one cell that holds characters, and it does not know how many tags are inside.
2. Tags 'java,spring,jpa' of post 1 LIKE '%java%': The string contains the characters java, so it matches the condition. This post really does have the java tag.
3. Tags 'java' of post 2 LIKE '%java%': The string contains the characters java, so it matches the condition. This post really does have the java tag.
4. Tags 'javascript' of post 3 LIKE '%java%': The string contains the characters java, so it matches the condition. But the tag of this post is javascript. LIKE only looks at whether the characters are inside, and does not look at whether it equals one of the tags split at the commas.
5. SELECT id, title FROM post WHERE tags LIKE '%java%' ORDER BY id: 3 rows matched the condition. What should come back is post IDs 1, 2, but the result is different. Post 3, which does not have the java tag, got mixed in.

Result of this run

• When you put several values in one cell, the DB cannot handle them one by one. If you search for java with LIKE, javascript is matched too. If you use MySQL's FIND_IN_SET('java', tags), it compares with the pieces split at the commas, so only posts 1 and 2 come back, but because the condition puts the column into a function, even if tags has an index, it cannot be used, and every row is read.

Code

-- Tables
CREATE TABLE post (
  id    INT PRIMARY KEY,
  title VARCHAR(50) NOT NULL,
  tags  VARCHAR(100)
);

-- Initial data
INSERT INTO post (id, title, tags) VALUES
  (1, 'Spring 101', 'java,spring,jpa'),
  (2, 'Java Basics', 'java'),
  (3, 'Building UIs', 'javascript');

-- Find what is connected
SELECT id, title
  FROM post
 WHERE tags LIKE '%java%'
 ORDER BY id;

Assumptions of this model

This is a small example with 3 members, 3 posts, and 5 tags. For all 14 combinations (7 designs × 2 tasks), the SQL on the screen was run as it is on a real MySQL 8.4.11 (InnoDB), and the error number and number of rows changed of the write statement, all the rows of the query result, and all the rows of each table afterwards were checked to be the same as in the model. "What should come back" is a fact decided in this example (Alice wrote posts 1 and 2, the java tag is on posts 1 and 2, and a member has one profile), and it is not a value the DB tells you. The following points in the summaries were also confirmed by running them separately on the same MySQL. With FIND_IN_SET on the comma-separated string, only posts 1 and 2 come back. Even when an index is created on tags and 2,000 rows are inserted, the execution plan of the FIND_IN_SET condition does not use the index and reads every row (type is ALL). In the comma-separated string, adding a tag that is already there is accepted as it is. In the associative table, inserting the same connection once more is rejected with error 1062, and connecting to a tag that does not exist is rejected with error 1452. That LIKE also matches javascript is the same in any database, but FIND_IN_SET is a MySQL function. The error numbers and messages are also MySQL's. The design that puts the foreign key of a one-to-many relationship on the "one" side is an example made to show a mistake. Which table to put the foreign key in for a one-to-one relationship, and whether to merge the tables, is a design decision that depends on how the data is queried, and this lab does not go as far as that comparison. Normalization and anomalies are covered in the "Normalization" lab.

Previous lab: Tables, keys, and constraints · Next lab: Normalization