Table structure for addresses

Question

Hi there, I currently have a table of customers. A customer can have  two different addresses, mailing and physically address. I want to ask your advice on how to structure the address information. All customers will have both a physical and a mailing address filled in. Most of the time, both addresses are the same, but there are a few occasions where the mailing address is different. My current table has both types of address all on the customer table. Is this ok to do or should I have a separate table for addresses and a join table for the two types of address?

Answer 1

I just want to make sure you have the same understanding as me of the setup

Customer_Addresses

---

AddressId

CustomerId

AddressTypeId

Number

Street

Unit

CityId

...

AddressTypes

---

AddressTypeId

AddressType

Answer 2

What would i do if the mailing address os the same as the physical afdress?

Answer 3

Yes Dan, that is what I have in mind if having to create the address tables. Thank you.

Answer 4

Thank u, Dan. Part of me knew that is the right practice, and part of me is trying to avoid having to create two more tables just for addresses since both addresses will always be completed  for each customer.

Answer 5

I'd created a new Addresses table with all the address fields, plus one to indicate the type of address, with a foreign key relating to the customers table.

You could even have a flag for mailing address is the same as the physical address.