How to design a database schema that represents ADDRESS?

Question

at address I mean only the usual address, for example, country, state, city, district, street, building,

where can the address be effectively referenced in other tables, such as people, so that we can select people in the same city or so on? thanks.

Answer 1

Well depends on how you want to normalize db (warehouse or transactions)

Example 1: irregular, all in one table

table name: user

: userid, username, country

sql to retrieve:

select username from user where country="USA" 

Example 2: normalized, all in a separate table

table name: user attribute: userid, username, countryID

table name: country attribute: countryID, country name

sql to retrieve:

  select username from user inner join country where country="USA" 

You need to know what db is used to determine the "effective" way.

Answer 2

In my experience, you need a country, state, city, zip code, address.

Only the first three / four are convenient for filtering users. Enum fields are very suitable for the first two. The following two options are perfectly tested using the APIs - this saves you from having to maintain a list of valid values.

I have not yet encountered any system (although I assume that the post office will need it along with geolocation), which requires that the address part be divided into separate parts of the data for more accurate filtering - plus each user has his own way input last.

Keep in mind that in some countries there is no state; that others do not have zip codes; and that postal code formats vary widely from country to next.

Also keep in mind that even if a user can have multiple addresses on your system, the last thing you want is to associate multiple users with the same id_address. They are usually better placed as information about users (or their company) or related 1-n details; never nn. When not, the user interface quickly penetrates it, and someone will constantly change the address of user B by mistake, because the latter has the opportunity to share it with user A.

Answer 3

Here is one extended database structure for representing addresses,
Benefits with this approach
1. You can add a city, country, state later.
2. It supports editing of an urban country or state.
3. The city is compared with the state, and a similar state is compared with the Country. That way, you just keep the city in addrss. You do not need to save state and country in each address, thereby reducing redundancy.
4. You can create a list of states when the user selects a country. Similarely you can create a list of cities when the user selects a state.

 Address id INT PK AUTO_INCREMENT street VARCHAR city_fk INT FK Zip_code VARCHAR City id INT PK AUTO_INCREMENT name VARCHAR state_fk INT FK State id INT PK AUTO_INCREMENT name VARCHAR country_fk INT Fk Country id INT PK AUTO_INCREMENT name VARCHAR user id INT PK AUTO_INCREMENT # other details user_address_mapping # So that user can have multiple address id INT PK AUTO_INCREMENT user_fk INT FK # Link to user address_fk INT FK # Foreign key to address 

EDIT: (Thanks @Denis comment)
Or, if your rivalry has no states (or you want a general solution), here is the structure.

 Address id INT PK AUTO_INCREMENT street VARCHAR city_fk INT FK state_fk INT FK country_fk INT FK Zip_code VARCHAR City id INT PK AUTO_INCREMENT name VARCHAR State id INT PK AUTO_INCREMENT name VARCHAR Country id INT PK AUTO_INCREMENT name VARCHAR user id INT PK AUTO_INCREMENT # other details user_address_mapping # So that user can have multiple address id INT PK AUTO_INCREMENT user_fk INT FK # Link to user address_fk INT FK # Foreign key to address # Here user_fk & address_fk should be composite unique key, so that users can not share an address.