M
M
Mikhail Abroskin2016-03-25 12:52:45
MySQL
Mikhail Abroskin, 2016-03-25 12:52:45

How to properly normalize a database?

Good day, good people!
Give me good advice, please. I took up, for the first time in my life, the normalization of a small database, and ran into a problem that I can’t figure out how to solve.
So, in order. It is necessary to design a small database of some equipment, and the database is designed for the whole of Russia, divided by regions. Those. the meaning is this -
there is a certain model of equipment, for example, let's call it "Wifi router model No. 1", and this model may be present in some regions, or may not be present.
Let's take, for example, 3 regions: Udmurtia, Bashkiria and Adygea (no hidden meaning, just to have monosyllabic names, to simplify writing).
And in Udmurtia and Bashkiria, we have our router, but not in Adygea ...
And the model number 2 router is present in Bashkiria and Adygea, but it is not in Udmurtia.
Accordingly, we have two tables - the "Equipment" table and the "Regions"
table in the equipment table, there are the following fields:
id Name Region
in the table Regions of the field are:
id Region
Accordingly, the table of regions will look like this:
Table - Regions
id Region
18 Udmurtia
2 Bashkiria
1 Adygea
Equipment table
id Name Region
1 Wifi Router m.1 18, 2
2 Wifi Router m.2 1, 2
Accordingly, it is confusing that multiple values ​​​​are indicated in the Region field, the Equipment table, and I can’t find a way to normalize this field, it turns out either as it is now, or to duplicate the equipment model for each region, which is even worse ...
How to be in this situation? I did not find a solution in Google and Yandex, so I decided to write here.
PS. Please do not pay attention to the fact that, for example, the Name field can also be normalized by parsing it to the
Equipment type and model, this will be done by itself. The question is only in the field with the Region, what are the options for normalizing it?
Thanks in advance)

Answer the question

In order to leave comments, you need to log in

4 answer(s)
A
Anton Shcherbakov, 2016-03-25
@miker059

Table 1 - equipment
id ...
Table 2 - regions
id ...
Table 3 - connection equipment-region
id vol. - ID reg.

D
Dmitry Kovalsky, 2016-03-25
@dmitryKovalskiy

And why not the table "Equipment in the region"? device id, region id.

Z
Zhainar, 2016-03-25
@zhainar

Enter the third table:
id | region_id | equipment_id | Availability

S
sim3x, 2016-03-25
@sim3x

before asking to normalize the data structure, indicate in what form you need to bring everything
https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D...
for 3NF https:/ /en.wikipedia.org/wiki/Many-to-many_(data_model)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question