What is the best way to organize a large database of addresses?
What is the best way to organize a large database of addresses?
It is necessary to create a database of mysql addresses. How best to organize? I have two options: 1) cuontries
id|name
1 |Russia
cities
id|name
1 |Moscow
2 |Saratov
villages
id|name
streets
id|name
1 |Lenin st.
places
id|name |country_id|city_id|village_id|street_id|building_number|office|flat_number|room_number
1 |somebuilding |1 |1 |NULL |1 |31 |12a |NULL |NULL
For simplicity, I am not using all addresses. If any part does not participate in the address, it is NULL
2)
addressElements
id|name
1 |country
2 |city
3 |village
4 |street
5 |office
6 |flat_number
7 |room_number
addressValues
id|addressElement_id|value
1 |1 |Russia
2 |2 |Saratov
3 |2 |Moscow
4 |3 |Prostokvashino
5 |4 |Lenin st.
places
id| name
1 | somebuilding
places_has_addressValues
place_id|addressValue_id
1 |1
1 |3
1 |5
UPD. I decided to do the following alt text http://notme.ru/bebe/addresses.jpg
I am using the "type" property (ENUM) to declare the type of an object. For example, the type of apartment could be "flat" or "room" or "office"
a source to share
The reasons why I choose the first option:
- Simplicity. The first option is about ten times clearer and more obvious. Think about how other people will support your code later.
- joins. As Vanessa mentioned, composing unions would be much more difficult with the second option.
The disadvantage of the first option is probably a more rigid structure, so you need to spend some time to make sure that you have all the options that are appropriate for your data model (for example, what about the PO field?). You might want to visit your local post office and chat. Perhaps http://www.russianpost.ru/ will have useful information?
a source to share
I would say with the first one. The place is in one country, one city (or perhaps no city), one village (or perhaps no village), etc. The second database will give you the flexibility to take place in multiple countries, on multiple streets, etc. But since it doesn't make any sense for addresses, it just adds an unnecessary layer of complexity and more joins in every time you want to find an address.
a source to share
The second option would be a performance killer, don't (EAV tables are high on my DO NOT DO list right there with cursors). It will also be difficult to ask for an address since you don't know how many items a particular address has.
How are you going to use it? Are you really going to get people to search for every possible street address to pick the right one to write? Why not just enter them in the address? I would not use a street table if you are going to do data entry from a GUI and not for bulk import. I somehow have the same attitude to the table of cities, how many records will it have? It's nice to try to stick to the cities that should exist for this country, but how practical is it when entering data? What do you do when someone needs to enter a city that is not in the city table? Cites sometimes change names, and the data record may have a newer value that has not yet entered the city table.
a source to share
1) Village and city are equivalent - that is, city or village, but not both.
2) A properly normalized db would have:
table countries
( country_id int primary key, country_name varchar(100) );
table cities # also villages
( city_id int primary key, city_country_id int, city_name varchar(200),
city_is_village bool );
table streets
( street_id int primary key, street_city_id int, street_name varchar(200) );
table addresses # we'll stop normalizing here
( addr_id int primary key, addr_street_id int, addr_building_number int,
addr_office int, addr_flat_number int, addr_room_number int );
# then to get the entire address, you would join the tables
SELECT addr_room_number, addr_flat_number, addr_office, addr_building_number,
street_name, city_name, city_is_village, country_name
FROM addresses
LEFT join streets ON street_id = addr_street_id
LEFT join cities ON city_id = street_city_id
LEFT join countries ON country_id = city_country_id
;
a source to share