Order and Customer Detail Relationships

VentureFree · #1 (**permalink**) 10-08-09, 09:41

I'm creating a database which includes the following tables (using Sql Server syntax and with extraneous info removed)

Code:

CREATE TABLE [Order](
	[Order_ID] [bigint]  IDENTITY(1,1) NOT NULL,
	[Customer_ID] [bigint] NOT NULL --,
	--Other columns defining Order specific details
)
CREATE TABLE [Customer](
	[Customer_ID] [bigint] IDENTITY(1,1) NOT NULL --,
	--Other columns defining Customer specific details
)
CREATE TABLE [Address](
	[Address_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Customer_ID] [bigint] NOT NULL --,
	--Other columns defining Address specific details
)
CREATE TABLE [Email](
	[Email_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Customer_ID] [bigint] NOT NULL --,
	--Other columns defining Email specific details
)
CREATE TABLE [Phone](
	[Phone_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[Customer_ID] [bigint] NOT NULL --,
	--Other columns defining Phone specific details
)

The relationships are pretty simple:

Order (1..*)---(1) Customer
Customer (1)---(1..*) Address
Customer (1)---(0..*) Email
Customer (1)---(1..*) Phone

No big deal, right? The problem is that for a given order there is only one customer (obviously), but I need a way to easily define which address email and phone to use for a specific order. There will be one address indicating the shipping address for an order, one phone number for contacting the customer, and zero or one email address also for contacting the customer. Logically it would resemble the following (taking the Customer out of the middle)

Order (1..*)---(1) Customer
Order (1..*)---(1) Address
Order (1..*)---(0..1) Email
Order (1..*)---(1) Phone

Having a separate key for each in the Order table seems somehow wrong to me. What's the best way to map this info? Should I make a bridge like the following?

Code:

CREATE TABLE [CustomerDetail](
	[Order_ID] [bigint] NOT NULL,
	[Customer_ID] [bigint] NOT NULL,
	[Address_ID] [bigint] NOT NULL,
	[Email_ID] [bigint] NULL,
	[Phone_ID] [bigint] NOT NULL
)

Or is that unnecessarily complicated? Should I just go with the separate key for each address/email/phone?

I hope this wasn't too confusing. I'm not too good at simplifying ideas for others.

MarkATrombley · #2 (**permalink**) 10-08-09, 15:04

I would just add Address_ID, Email_ID, and Phone_ID to the Order table since the specific address, email, and phone number to use are directly related to the order. Creating a CustomerDetail table is just adding complexity for no reason.

Internet Services

Web Development

Digital Marketing

Consumer Tech