how to design the database schema representing ADDRESS elegantly?

Question

By address I only mean normal address like country, state, city, district, street, building where the address can be efficiently referenced in other tables like people so that we can select people in the same city or so on? Thx.

Answer 1

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

Only the first three/four are convenient to filter users. Enum fields are very suitable for the first two. The next two are ideally validated using APIs -- this will spare you the hassle of needing to maintain a list of valid values.

I've yet to run into any system (though I assume a post office would need it, along with geolocation) that needs the address part to be chunked in individual pieces of data for more precise filtering -- plus, each user has his own way of entering the latter.

Keep in mind that some countries have no state; that others have no zip codes; and that zip code formats vary widely from a country to the next.

Also keep in mind that, even when a user can have multiple addresses in your system, the last thing you want is to tie multiple users to the same address_id. They're usually better placed as details of the users (or their company), or as 1-n related details towards the latter; never n-n. When not, UI issues quickly creep in, and someone will invariably edit the address of user B by mistake, because the latter happens to share it with user A.

Answer 2

Here is one extended database structure for address representation,
Advantages with this approach
1. You can add city, country, state later on.
2. it supports edting of city country or state.
3. City is mapped to State and similarely state is mapped to Country. So you will just be storing city in an addrss. You are not needed to store state and country in each address, thus reducing redundancy.
4. You can generate a list of State whenever user chooses a country. Similarely you can generate a list of city when user chooses 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 to @Denis comment)
Or If your contry does not have states (Or you want a generic 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.