Databases
StackOverflow2013
Postsdbo
POHow to best represent addresses in a database
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to best represent addresses in a database
    text
    copied!<blockquote> <p><strong>Possible Duplicates:</strong><br> <a href="https://stackoverflow.com/questions/929684/is-there-common-street-addresses-database-design-for-all-addresses-of-the-world">Is there common street addresses database design for all addresses of the world?</a><br> <a href="https://stackoverflow.com/questions/24481/what-is-the-best-way-to-store-international-addresses-in-a-database">What is the &ldquo;best&rdquo; way to store international addresses in a database?</a><br> <a href="https://stackoverflow.com/questions/126207/best-practices-for-consistent-and-comprehensive-address-storage-in-a-database">Best practices for consistent and comprehensive address storage in a database</a> </p> </blockquote> <p>I currently have four tables, Customers, Contacts, Facilities and Clients.</p> <p>Each of these tables has the following fields: AddressLine1, AddressLine2, City, StateOrProvince, PostalCode.</p> <p>I would like to move the addresses out to a separate table and be able to also designate the type of address (billing, shipping, main, etc.).</p> <p>My solution is as follows:</p> <ol> <li>Remove AddressLine1, AddressLine2, City, StateOrProvince, PostalCode from Customers, contacts, Facilities and Clients.</li> <li>Create Addresses table with fields AddressID(PK), AddressLine1, AddressLine2, City, StateOrProvince, PostalCode , LastUpdateUser, LastUpdateTime.</li> <li>Create AddressTypes table with fields AddressTypeID, AddressTypeName, AddressTypeDescription, AddressTypeActive, LastUpdateUser, LastUpdateTime</li> <li>Create CustomerAddresses table with fields CustomerID, AddressID, AddressTypeID, CustomerAddressActive, LastUpdateUser, LastUpdateTime</li> <li>Create ClientAddresses table with fields ClientID, AddressID, AddressTypeID, ClientAddressActive,LastUpdateUser, LastUpdateTime</li> <li>Create ContactAddresses table with fields ContactID, AddressID, AddressTypeID, ContactAddressActive, LastUpdateUser, LastUpdateTime</li> <li>Create FacilityAddresses table with fields FacilityID, AddressID, AddressTypeID, FacilityAddressActive, LastUpdateUser, LastUpdateTime</li> </ol> <p>I am looking for guidance to determine if there is a better solution than the one I devised. Why does everyone think?</p> <p>EDIT: I am not concerned with anything outside the US at this point and not concerned with how to store the street address, ie street number vs the whole street address. I am concerned from a database design and table structure stand point.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload