Three customer addresses in one table or in separate tables?

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)

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.

I'd go with denormalized. It's easier.

If you normalize it, the address table would require you to remove duplicates and relink records from the customer table. If I had the same delivery and invoice address, it seems like it should link to the same record. If I change one, you're required to:

1. Create a new address record.
2. Relink the customer record.

If I change it back you need to check:

1. Does a similar address entry already exist.
2. Relink the customer record.

This programming overhead seems to obviate the advantage of less space that normalization seems to offer. A denormalized solution, like you pointed out, would provide faster queries and easier maintenance (programming-wise). This seems to be the deciding factor for me.

A normalized solution, like pointed out above, would allow you to add more addresses later. But you'd have to add a field for the foreign key anyways, unless you planned on organizing the tables without linkage from the customer to the address table.

Advantages of Normalized

• Less space.
• Duplicate logic kinda built in (though maybe they weren't actually duplicates?)
• Support addition of new address fields (kinda).

Advantages of Denormalized

• Faster queries.
• Less programming.