Three customer addresses in one table or in separa

Question

In my application I have a Customer class and an Address class. The Customer class has three instances of the Address class: customerAddress, deliveryAddress, invoiceAddress.

Whats the best way to reflect this structure in a database?

• The straightforward way would be a customer table and a separate address table.
• A more denormalized way would be just a customer table with columns for every address (Example for "street": customer_street, delivery_street, invoice_street)

What are your experiences with that? Are there any advantages and disadvantages of these approaches?

Answer 1

I'd go (as database theory teaches) for two separate tables: Customer and Address.

The idea of putting three fields in the Customer table is bad, as you say, because it would violate normalization rules (and fail when addresses would become more than three).

edit: also, I'd split the Address table record in several fields, one for the toponomastic prefix, one for the street, etc. and put a unique key on those. Otherwise, you'd end with a database full of duplicates.