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?
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.
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.