Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    4

    Unanswered: Serial Number Tracking Database Design

    Hi All Database Professional,

    I need some help in designing a Serial Number Tracking Database,

    All new component come with a Serial Number(Primary Key) pasted on it, after 1 years, we will sent it for Refurbish,
    after Refurbished, the Serial Number of the component will change to a New Serial Number(New Primary Key).
    I want a function to trace back the component Serial Number changed History, like I can use the latest Serial Number to trace back the Original Serial Number, some times the component Refurbished 5 times, so I need to trace back the Original Serial Number with the fifth Serial Number.

    please teach me to design this database.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why settle for an arbitary 5 times?
    Instead have a child table that contains tge serial number
    Use an internal primry key which is unchanging.
    Use that primary key with the serialnumber as a composite primary key in the child table, so that identifies the product with a specific serialnumber. The serialnumber in this child table should have a unique constraint so there can only ever be one instance of tge serialnumber. Id also store other relevant data eg period from or to whenthat serialnumber was valid. Think of it as a one sided intersection table
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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