Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013

    ID + Name as PK?

    This topic must be fairly common but I can't seem to find what I'm looking for.

    Table Products has columns ProductID (PK), ProductName, ...
    ProductID is FK in other tables.

    This works well as long as ProductID=1234 and ProductName="Twinkies". However, marketing will eventually change "Twinkies" to "Delicious Twinkies". Same physical product BUT they want the same ProductID with a different name/spin.

    What's the best way to handle this?

    - Set ProductID + ProductName as PK and tell marketing that if they change the ProductName, they must accept a new ProductID?

    - Keep ProductID unique, allow ProductName to change, and stuff current ProductID & ProductName into transaction history table?

    - ?

    It seems that ProductID + ProductName as PK is correct but it doesn't feel right to me.

    This is for a commercial product with at least 1K databases in the wild. The current implementation is ProductID (PK). If a schema change is required, the question of "how to get there from here" is a factor.

    Thanks for your help.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    I would leave the PK as the ID, and assign a new ID whenever the product name (or any other important attribute) changes. This would keep the base schema intact, and relationally sound.

    Add a productFamily table, something like:
    CREATE TABLE ProductFamilies (
       ProductID    widget          NOT NULL
       CONSTRAINT FK01ProductFamilies
          FOREIGN KEY (ProductId) REFERENCES Products (ID)
    ,  FamilyID     widget          NOT NULL
       CONSTRAINT FK02ProductFamilies
          FOREIGN KEY (FamilyId) REFERENCES Products (ID)
    ,  EffDate      DATETIME        NOT NULL
       CONSTRAINT PKProductFamilies
          PRIMARY KEY (ProductID)
    This will allow you to handle the changes in ID number as a family (essentially the products with multiple possible product numbers that you want to treat as a single product as it evolves over time). I'd recommend that you use the initial product ID as the FamilyID, although I've seen cases where the current product ID is used instead.

    Oh yeah, adjust the widget to the appropriate data type!

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2013
    Thanks, Pat. Problem solved.

    I especially appreciate the tip about choosing the base FamilyID.

Posting Permissions

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