Three customer addresses in one table or in separate tables?

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?

Answers


If you are 100% certain that a customer will only ever have the 3 addresses you described then this is OK:

CREATE TABLE Customer
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(60) not null,
    customerAddress int not null
    	CONSTRAINT FK_Address1_AddressID FOREIGN KEY References Address(ID),
    deliveryAddress int null
    		CONSTRAINT FK_Address2_AddressID FOREIGN KEY References Address(ID),
    invoiceAddress int null
    		CONSTRAINT FK_Address3_AddressID FOREIGN KEY References Address(ID),
    -- etc
)

CREATE TABLE Address
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Street varchar(120) not null
    -- etc
)

Otherwise I would model like this:

CREATE TABLE Customer
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(60) not null
    -- etc
)

CREATE TABLE Address
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    CustomerID int not null
    	CONSTRAINT FK_Customer_CustomerID FOREIGN KEY References Customer(ID),
    Street varchar(120) not null,
    AddressType int not null 
    -- etc
)

Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.