[ACCEPTED]-Is this a good way to model address information in a relational database?-rdbms

I actually use this as one of my interview 16 questions. The following is a good place 15 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 14 are totally unaware of how they are being 13 used, and your entities (Users, Accounts) don't 12 directly know anything about addresses either. It's 11 all up to the linking tables you create 10 (UserAddresses in this case, but you can 9 do whatever fits your model).

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

Option 2, without a doubt.

Some important 38 things to keep in mind: it's an important 37 aspect of design to indicate to the users 36 when addresses are linked to one another. I.e. corporate 35 address being the same as shipping address; if 34 they want to change the shipping address, do 33 they want to change the corporate address 32 too, or do they want to specify a new loading 31 dock? This sort of stuff, and the ability 30 to present users with this information and 29 to change things with this sort of granularity 28 is VERY important. This is important, too, about 27 the updates; give the user the granularity 26 to "split" entries. Not that this sort 25 of UI is easy to design; in point of fact, it's 24 a bitch. But it's really important to do; anything 23 less will almost certainly cause your users 22 to get very frustrated and annoyed.

Also; I'd 21 strongly recommend keeping around the old 20 address data; don't run a process to clean 19 it up. Unless you have a VERY busy database, your 18 database software will be able to handle 17 the excess data. Really. One common mistake 16 I see about databases is attempting to overoptimize; you 15 DO want to optimize the hell out of your 14 queries, but you DON'T want to optimize 13 your unused data out. (Again, if your database 12 activity is VERY HIGH, you may need to have 11 something does this, but it's almost a certainty 10 that your database will work well with still 9 having excess data around in the tables.) In 8 most situations, it's actually more advantageous 7 to simply let your database grow than it 6 is to attempt to optimize it. (Deletion 5 of sporadic data from your tables won't 4 cause a significant reduction in the size 3 of your database, and when it does... well, the 2 reindexing that that causes can be a gigantic 1 drain on the database.)

Do you want to keep a historical record 8 of what address was originally on the purchase 7 order?

If yes go with option 1, otherwise 6 store it in the supplier table and link 5 each purchase order to the supplier.

BTW: A 4 sure sign of a poor DB design is the need 3 for an automated job to keep the data "cleaned 2 up" or in synch. Option 2 is likely a bad 1 idea by that measure

I think I agree with JohnFx..

Another thing 15 about (snail-)mail addresses, since you 14 want to include country I assume you want 13 to ship/mail internationally, please keep 12 the address field mostly freeform text. It's 11 really annoying having to make up an 5 digit 10 zip code when Norway don't have zip-codes, we 9 have 4 digit post-numbers.

The best fields 8 would be:

• Name/Company
• Address (multiline textarea)
• Country

This should be pretty global, if 7 the US-postal system require zip-codes in 6 a specific format, then include that too 5 but make it optional unless USA is selected 4 as country. Everyone know how to format 3 the address in their country, so as long 2 as you keep the linebreaks it should be 1 okay...

Why would any of the rows on the address 5 table become unused? Surely they would 4 still be pointed at by the purchase order 3 that used them?

It seems to me that stopping 2 the duplicates should be the priority, thus 1 negating the need for any cleanup.

In the case of orders, you would never want 25 to update the address as the person (or 24 company) address changed if the order has 23 been sent. You meed the record of where 22 the order was actually sent if there is 21 an issue with the order.

The address table 20 is a good idea. Make a unique constraint 19 on it so that the same entity cannot have 18 duplicate addresses. You may still get them 17 as users may add another one instead of 16 looking them up and if they spell things 15 slightly differently (St. instead of Street) the 14 unique constraint won't prevent that. Copy 13 the data at the time the order is created 12 to the order. This is one case where you 11 want the multiple records because you need 10 a historical record of what you sent where. Only 9 allowing inserts and deletes to the table 8 makes no sense to me as they aren't any 7 safer than updates and involve more work 6 for the database. An update is done in one 5 call to the database. If an address changes 4 in your idea, then you must first delete 3 the old address and then insert the new 2 one. Not only more calls to the databse 1 but twice the chance of making a code error.

I've seen every system using option 1 get 2 into data quality trouble. After 5 years 1 30% of all addresses will no longer be current.