Linking Database Tables in Python

Let's say 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 disadvantage of this table is that the same city is repeated several times for different users. This leads to some problems.

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

Secondly, it is quite inconvenient to perform operations with cities. For example, we want to display a list of all cities. It is not so easy to do. We will have to get all users together with their cities, then delete duplicates from the received cities and only then we will get this list.

Now let's imagine that the database contains 10000 users from 10 cities - for the sake of these 10 cities we will have to get the entire table from a huge number of rows - this will be a very slow operation with a senseless waste of resources.

Solution to the problem

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

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

cities
id	name
1	city1
2	city2
3	city3

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

Practical tasks

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

Let's say you need to store rivers and the seas these rivers flow into. Describe the storage structure.

Let's say you need to store cities and the countries they are in. Describe the storage structure.