Best practices for storing postal addresses in a database (RDBMS)?

by

For more international use, one schema to consider is the one used by Drupal Address Field. It’s based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative areas ( province, state, oblast, etc ) with ISO codes.

Here’s the gist of the schema, copied from the module page:

country => Country (always required, 2 character ISO code)
name_line => Full name (default name entry)
first_name => First name
last_name => Last name
organisation_name => Company
administrative_area => State / Province / Region (ISO code when available)
sub_administrative_area => County / District (unused)
locality => City / Town
dependent_locality => Dependent locality (unused)
postal_code => Postal code / ZIP Code
thoroughfare => Street address
premise => Apartment, Suite, Box number, etc.
sub_premise => Sub premise (unused)

A lessons I’ve learned:

  • Don’t store anything numerically.
  • Store country and administrative area as ISO codes where possible.
  • When you don’t know, be lax about requiring fields. Some country may not use fields you take for granted, even basic things like locality & thoroughfare.

More Related Contents:

  1. How should international geographical addresses be stored in a relational database?
  2. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  3. NoSql vs Relational database
  4. Best representation of an ordered list in a database?
  5. What’s the difference between identifying and non-identifying relationships?
  6. Can I have multiple primary keys in a single table?
  7. What’s wrong with foreign keys?
  8. What are database normal forms and can you give examples? [closed]
  9. Difference between scaling horizontally and vertically for databases [closed]
  10. Multiple schemas versus enormous tables [closed]
  11. What are the design criteria for primary keys?
  12. Is there common street addresses database design for all addresses of the world? [closed]
  13. Techniques for database inheritance?
  14. What is the most efficient way to store tags in a database?
  15. Microservices with shared database? using multiple ORM’s? [closed]
  16. What are the advantages of using a single database for EACH client?
  17. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  18. Still Confused About Identifying vs. Non-Identifying Relationships
  19. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  20. Ideas on database design for capturing audit trails [closed]
  21. Alternatives to Entity-Attribute-Value (EAV)?
  22. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  23. Versioning Database Persisted Objects, How would you? [closed]
  24. Best design for a changelog / auditing database table? [closed]
  25. What’s the better database design: more tables or more columns?
  26. Should OLAP databases be denormalized for read performance? [closed]
  27. Best beginner resources for understanding the EAV database model? [closed]
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment