Multilingualism in the real estate database?

Question

MySQL

Guys, help me solve the dilemma. It is
required to organize the database structure for a real estate agency. Website in 3 languages. Search for apartments by many parameters: price, floor, water, etc.
Proposed option: three localized records of the same apartment with all possible parameters are made in the flats
table . Cities, districts - also in three versions as separate entries. It turns out there is only one apartment, but there are three records. The city is actually one, but the entries are three. ID | STREET | LANG | PRICE | FLOOR | CITY_ID .... ---+----------------------+-------+---------- +---------+----------- 1 | Tsentralnaya street ru 25000 5 1 1 | vul. Tsentralna ua 25000 5 2
1 | Center street en 25000 5 3
It still seems to me that the right decision would be to create one entity each (city, apartment) and three translation records
flats
ID | CITY_ID | PRICE | FLOOR
---+------------------------+--------+
1 | 1 25000 5
2 | 1 15000 1
3 | 1 30000 1
translations
ID | STREET | LANG | FLAT ID |
---+----------------------+-------+----------+
1 | Tsentralnaya street ru 1
2 | vul.Tsentralna ua 1
3 | Center street en 1
But I can’t prove why the second option is better (and is it better?) - I can’t. Can someone help

Answer 1

Certainly, several records for one object - at least it is illogical. And assume that there will always be 3 languages ​​too. The proposal is simple - choose the main language (in which employees work, the one in which the object must be named) and create a translation table:
language ( id ; language )
translation ( id AUTO_INCREMENT ; language ; table ; string ; value )
I.e. you created the city "Moscow" - just one and in the interface next to the "translations" button - a pop-up window opens with other languages ​​​​(ua, en) translations are entered, saved, the button is repainted (red - no translations; gray - partially translated; blue - all translated) and title at the same time can be used to inform. And the entries in the database are the following
cities ( 1 , 'Moscow' - chose the main one - Russian )
translation ( * , 'city' , 1 - id of the record in the city table , 1- for Ukrainian , 'Moscow' - probably the same)
translation ( * , 'city' , 1 - the same id of the same Moscow, 2- for English, 'Moscow')
translation (*, 'streets', 1, 2-for English, 'Central st.') - i.e. translated the street "central" in the street table