locked
1:1 relationship ERD with a 3rd entity RRS feed

  • Question

  • I am trying to solve this problem but I am a bit lost as to where and what the primary keys are:

    Each order is billed on one invoice, and each invoice is a bill for exactly one order (by only one customer).

    I know invoice and order have a 1:1 relationship, but what exactly will the primary key be? An where does customer fits in?

    Monday, July 2, 2012 11:11 PM

Answers

  • The OrderID will be the Foreign key and the InvoiceID will be the primary key.

    The 1:1 relationship is the same as 1:many relationship. You can enforce 1:1 using a trigger (to reject new inserts if there is 1 row already for the OrderID).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Syed Qazafi Anjum Monday, July 2, 2012 11:38 PM
    • Marked as answer by Iric Wen Thursday, July 12, 2012 3:17 AM
    Monday, July 2, 2012 11:35 PM

All replies

  • You can have 

    Customer table

    Orders table (OrderID, CustomerID, OrderTotalQty, OrderTotalBalance, etc.)

    Invoices (InvoiceID, OrderID, ....)

    -------------------

    Since you have 1 invoice per order, it implicitly means, that it will be 1 customer per invoice. You don't need to include CustomerID into Invoices table (it's redundant info), although you can (breaking normalization, but for simplicity of retrieval customer's invoices).

    If you include CustomerID into Invoices table, you need to make sure that CustomerID matches in Both Orders and Corresponding Invoices.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Monday, July 2, 2012 11:22 PM
  • Because of the 1:1 relationship being formed, in the invoice table will the orderID be a foreign Key of a Primary Foreign Key? I thought 1:1 relationships have the same primary key? 
    Monday, July 2, 2012 11:30 PM
  • The OrderID will be the Foreign key and the InvoiceID will be the primary key.

    The 1:1 relationship is the same as 1:many relationship. You can enforce 1:1 using a trigger (to reject new inserts if there is 1 row already for the OrderID).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Syed Qazafi Anjum Monday, July 2, 2012 11:38 PM
    • Marked as answer by Iric Wen Thursday, July 12, 2012 3:17 AM
    Monday, July 2, 2012 11:35 PM
  • o ok, I see. Thank you
    Monday, July 2, 2012 11:36 PM
  • The correct way to do a data model is to have separate tables for all relationship. This includes 1:1 relationships, too. The relationship can have its own attributes; a marriage. There is one man and one woman but the marriage itself has a license, a date, a presiding authority,etc. 

    Here is the classic skeleton for your schema 

    CREATE TABLE Customers 
    (cust_id PRIMARY KEY);

    CREATE TABLE Orders 
    (order_nbr PRIMARY KEY
     cust_id REFERENCES Customers,
     FOREIGN KEY (order_nbr, cust_id)
     REFERENCES Billing (order_nbr, cust_id));

    CREATE TABLE Order_Details 
    (order_nbr REFERENCES Orders
     ON DELETE CASCADE,
     item_id
    PRIMARY KEY (order_nbr, item_id));

    The order details are a weak entity, they need the strong entity Orders to exist.

    CREATE TABLE Invoices
    (invoices_nbr PRIMARY KEY,
     cust_id REFERENCES Customers
     FOREIGN KEY (invoices_nbr, cust_id));

    Here is where it gets tricky. Your rule about the the 3-ary relationship in the Billings assure the invoice and order go to the same single costumier. I assume that the invoice is created after the order, so you start a dummy invoice number then update it later.

    CREATE TABLE Billings 
    (order_nbr REFERENCES Orders,
     invoice_nbr DEFAULT '00000' REFERENCES Invoices,
     cust_id REFERENCES Customers,
     UNIQUE (order_nbr, cust_id),
     UNIQUE (invoice_nbr, cust_id),
     PRIMARY KEY (order_nbr, invoice_nbr)); 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 3, 2012 3:32 PM