Search code examples
databasedatabase-designdatabase-schema

How to design the database schema representing ADDRESS elegantly?

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.

Solution

  • Well depend on how you want to normalize the db, (warehouse or transactions)

    Example 1: unnormalized, everything in one table

    table name: user

    attribute: userid, username, country

    sql to retrieve:

     select username from user where country="USA"

    Example 2: normalized, everything in separate table

    table name: user attribute: userid, user name, 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 the db is used for to determine the "efficient" way.