Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    27

    Order and Customer Detail Relationships

    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.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •