How it is better to organise a large database of addresses?

Posted by shurik2533 on Stack Overflow See other posts from Stack Overflow or by shurik2533
Published on 2010-04-20T14:07:25Z Indexed on 2010/04/21 9:03 UTC
Read the original article Hit count: 198

Filed under:
|
|

How it is better to organise a large database of addresses?

It is need to create mysql database of addresses. How it is better for organising? I have two variants: 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 simplification I use not all making addresses. If any part does not participate in the address it is equal 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 have decided to make as follows alt text

© Stack Overflow or respective owner

Related posts about mysql

Related posts about database-design