Include logical key on each table vs. join

database-designdenormalizationjoin;

I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the inventory item's ID, the receiving department table stores a shipment ID, the sales table stores the receiving receipt ID, and so on). I've designed the tables in this way to enforce the fact that each of the lifecycle steps must happen in a particular sequence.

If I wanted to check information about a particular inventory item, I'd need to go through and join all the tables that represent that item's lifecycle. Each of my inventory items has a unique serial number, so I'm thinking about including that SN on specific lifecycle event tables in order to avoid having to perform all the joins.

I was wondering, is there a rule or guideline as to when you should use key duplication vs performing a bunch of joins? If I were to go with the joining method, would this be an appropriate place to use a bunch of different views?

Best Answer

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence."

If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints.

Having independent keys is good for quickly getting a record specific to a table, but it seems you're not going to get a lot of the benefits of constraints by trying to 'roll your own'.

Related Solutions

How should I best design the tables and relationships, given the following rules

From what I can understand, apart from a Customer table, you'll need three tables:

Contract — holding the single-valued data about a contract, excluding receipts. It might record the latest receipt number for the contract, but that would be an optimization, storing derivable data. Primary Key: Contract Number (aka Lot Number).
Contract Items — holding the 1-6 items for the contract. Primary Key: Lot Number, Lot Sequence Number. Lot Number is a Foreign Key reference to Contract.
Receipts — holding information about receipts. Primary Key: Receipt Number. Foreign Key: Contract Number reference to Contract again.

A given receipt is associated with one contract; a single contract may have multiple receipts over time if it is a pawn contract (buy and sell contracts will have a single receipt).

Even if a new receipt is not issued when a pawn contract is cancelled, there'll need to be a database update of the contract or the receipt (or both) to indicate that the contract is cancelled.

Is there anything that I'm missing here?

How toNNER JOIN and OUTER JOIN within the same table

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT 
    m.month, 
    p.name, 
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity, 
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM 
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN 
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN  
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL 
   OR s.month IS NOT NULL

Related Question