Database Design for Tracking Component

[domingsun]

Hi All,

I want to design a database to track the component

First, the OEM data will be import into tbl_Part, this contain component Original serial number(Example : 40000001).

After 3 years, if I want to rebuild this part, I will given a new serial number for it(from 40000001 changed to 50000001).

After 2 years I rebuild this part again(from 50000001 changed to 60000001).

So now I want to trace back the Original serial number based on the latest serial number 60000001.

Anyone know how to design this database ?
I think it should have two tables, one is tbl_Part another one is tbl_Rebuild.

 

[plog]

Two tables is correct, but tbl_Rebuild sounds like a poor name. tbl_Build would be better in that you keep all builds of the part in there--including the first one.

Also, if your Serial number is always 2 parts like you illustrated (XY, where X is the version and Y is the rest of the serial), then it would be best kept in 2 fields and not all together.

 

[domingsun]

Two tables is correct, but tbl_Rebuild sounds like a poor name. tbl_Build would be better in that you keep all builds of the part in there--including the first one.

Also, if your Serial number is always 2 parts like you illustrated (XY, where X is the version and Y is the rest of the serial), then it would be best kept in 2 fields and not all together.

Hi,
can you show me some example ragarding to the database relationship design ?

 

[plog]

Relationship design? Its fairly straightforward, 2 tables: tbl_Part with an autonumber primary key and then tbl_Builds which uses tbl_Part.PrimaryKey as a foreign key to determine which part each build is for.