1

Right now I'm creating an application to manage the inventory. We receive products very often and usually they have different expiration date or price. Example:

RECEIPT    PROVIDER     PRODUCT       PRODUCT EXPIRATION DATE    PRICE   QUANTITY
2125       IQT          PLASTIC BAG   10/21/2012                 2.35    30
2126       MDU          PLASTIC BAG   05/03/2012                 2.45    60
2127       PRADO        PLASTIC BAG   10/21/2012                 2.30    10


PRODUCT    NAME          EXPIRATION DATE   PRICE  QUANTITY
1001       PLASTIC BAG   10/21/2012        2.35   30
1001       PLASTIC BAG   05/03/2012        2.45   50
1001       PLASTIC BAG   10/21/2012        2.30   10

My question is if I should make EXPIRATION DATE and PRICE Primary Keys also so I'm available to register products with different parameters. Or I should create a double PK of PRODUCT ID and RECEIPT ID.

rjzii
  • 14,236
  • 12
  • 79
  • 119
Washu
  • 835
  • 1
  • 9
  • 20
  • 1
    Primary keys are like Highlanders -- there can be only one. – Alex Howansky Nov 21 '12 at 15:43
  • then what can i do to be able to register the products with different data? Because is not only that they want me to keep the receipt and the information is also that they want to see it in the inventory. – Washu Nov 21 '12 at 15:47

1 Answers1

2

I'd argue that a composite key in a slowly changing dimension table is acceptable. I would not agree that a product's price is part of the composite key. (ProductID, ExpirationDate) would be fine. Some may argue that you implement an archive table (e.g. Product_Archive) instead and only keep the current record in the Product table.

You may elect to use a surrogate key in place of a SKU, UPC, or PLU in the primary key.

I would suggest that the product price is a candidate for a separate table because there are many driving factors on a products price. In brick and mortar environment your product price will vary based on location.

Take for example navel oranges. You can get them from multiple vendors based on things like the season or region which you purchased them from.

Rob Paller
  • 7,736
  • 29
  • 26
  • Ok I will use the Product ID, Expiration Date key and create the Archive table. – Washu Nov 21 '12 at 16:04
  • You only need the archive table if you are not going to keep the slowly changing dimensions in the same table. I think you misinterpreted my response slightly. – Rob Paller Nov 21 '12 at 16:17
  • ... I think i did sorry Rob I'm just starting to work in this.. and I haven't even finished my studies yet... is a big opportunity but is hard for me. Thanks – Washu Nov 21 '12 at 16:31