12-09-15, 22:02 #1Registered User
- Join Date
- Jul 2014
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.
12-10-15, 00:59 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 tableI'd rather be riding on the Tiger 800 or the Norton