Is this a good way to model address information in a relational database?

by

I actually use this as one of my interview questions. The following is a good place to start:

Addresses
---------
AddressId (PK)
Street1
... (etc)

and

AddressTypes
------------
AddressTypeId
AddressTypeName

and

UserAddresses (substitute "Company", "Account", whatever for Users)
-------------
UserId
AddressTypeId
AddressId

This way, your addresses are totally unaware of how they are being used, and your entities (Users, Accounts) don’t directly know anything about addresses either. It’s all up to the linking tables you create (UserAddresses in this case, but you can do whatever fits your model).

One piece of somewhat contradictory advice for a potentially large database: go ahead and put a “primary” address directly on your entities (in the Users table in this case) along with a “HasMoreAddresses” field. It seems icky compared to just using the clean design above, but can simplify coding for typical use cases, and the denormalization can make a big difference for performance.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  3. What is the purpose of system table master..spt_values and what are the meanings of its values?
  4. How to create multiple one to one’s
  5. What are best practices for multi-language database design? [closed]
  6. Database Structure for Tree Data Structure [closed]
  7. Database Design for Revisions?
  8. A beginner’s guide to SQL database design [closed]
  9. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  10. PostgreSQL: Which Datatype should be used for Currency?
  11. Why (and how) to split column using master..spt_values?
  12. Is there common street addresses database design for all addresses of the world? [closed]
  13. Dynamic Database Schema [closed]
  14. Should I design a table with a primary key of varchar or int?
  15. Relational database design question – Surrogate-key or Natural-key?
  16. Database EAV Pros/Cons and Alternatives
  17. How to create a multi-tenant database with shared table structures?
  18. Name database design notation you prefer and why?
  19. When to Denormalize a Database Design
  20. Best way to model Customer Address
  21. Select (retrieve) all records from multiple schemas using Postgres
  22. Use email address as primary key?
  23. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  24. Which database design gives better performance?
  25. PostgreSQL Index on JSON
  26. What does ON [PRIMARY] mean?
  27. Primary key/foreign Key naming convention [closed]
  28. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  29. can we have a foreign key which is not a primary key in any other table?
  30. How to get the byte size of resultset in an SQL query?

Leave a Comment