Linking Tables in Databases

Suppose we have a table with user names and the cities they live in:

users
id	name	city
1	user1	city1
2	user2	city1
3	user3	city2
4	user4	city1
5	user5	city3
6	user6	city2

The drawback of this table is that the same city is repeated several times for different users. This leads to some problems.

First, constant repetition causes the database to take up much more space.

Second, it is quite inconvenient to perform operations with cities. For example, we want to display a list of all cities. It's not so easy to do. We will have to get all users along with their cities, then remove duplicates from the obtained cities, and only then will we get this list.

Now imagine that the database has 10000 users from 10 cities - for these 10 cities we will have to retrieve the entire table with a huge number of rows - it will be a very slow operation with a meaningless waste of resources.

Solving the Problem

We need to split our table into two: one will store cities, and the other - users. In this case, the users table will have a column city_id, which will reference the user's city.

So, let's make two tables. The table with cities:

cities
id	name
1	city1
2	city2
3	city3

The table with users:

users
id	name	city_id
1	user1	1
2	user2	1
3	user3	2
4	user4	1
5	user5	3
6	user6	2

Suppose you need to store products (name, price, quantity) and categories of these products. Describe the storage structure.

Suppose you need to store rivers and the seas into which these rivers flow. Describe the storage structure.

Suppose you need to store cities and the countries they are located in. Describe the storage structure.