There is a database with addresses. I think over the structure. At the moment there are tables: Types of streets, Streets, Types of settlements, Locations. The question is this. Lenin Avenue in Krasnoyarsk and Lenin Avenue in Tomsk - these are different entities, so there should be such lines in the Streets table?

ID / City / Street type / Name

1/1/1 / Lenin

2/2/1 / Lenin

Or, in the Streets table, there is no need for the Locality column, and instead make a separate table for the connection [Street-Town] and Lenin Avenue will not be duplicated for each city?

  • what for different? "Green" in Novosibirsk and Sverdlovsk different entity? Essence one, the name. And in the table at the address is a link to the name of the street. Tomsk / 34 - where 34 is a link to a unique id with the name of the street - maint
  • Always try to have the database reflect the essence of the real world. Physically, the streets in different cities are different - it means that the database should be different. And the name is not an entity, it is an ordinary attribute. Tomorrow you want to add to the database at home or the geographical coordinates of points on the streets. To which you tie them, if the entities are names. And it is interesting for what you enter "street types". In your database, it depends on the type, i.e. For example, does it work only with prospectuses? Of course, you can store the full / abbreviated name "Street-Str" for them, well, that's all. - Mike

1 answer 1

These are completely different entities, and for this reason, in Ust-Tarapyginsk, they will want to rename “Lenin Street” into “Rotten Dead End”, and you will get a lot of funny problems with isolating this street from all other cities.

Moreover, you will need, for good, to take into account the "historicity", because some of the data will relate to the old name, and some - to the new.

Copy the KLADR or FAIS structure into the database, it will be easier for you.

  • Yes, I thought about it. However, in the end, he didn’t see any problems with the renaming. Renaming will mean changing the street identifier from one to another in the CityUdits table (which corresponds to the new name and type and will be added if there is no such street in the reference book yet). I did not even think about historicity, but I can offer one more development of events without thinking. The same guys you mentioned will decide to rename Lenin Street to Rotten dead end, and then they will realize how forgotten Lenin is and rename Pupkin Avenue to Lenin Street. What in this case will help the historicity? - iRumba
  • @iRumba in any case, the streets of Lenin in different cities are not equivalent in reality, except for the momentary coincidence of names. Therefore, it makes no sense to create an extra entity in the form of a bundle of many cities with one street. - gbg
  • This raises the question of data integrity. But what if I want to choose only a city without a street as an address? This means that in the table with addresses you need a link not only to the street, but also to the city. But then (theoretically) it will be possible to add a city identifier and a street identifier that is located in another city to the table. - iRumba