Experienced Database Admin Question

[techmanbd]

I am creating tables for a test program I am writing that stores the test data.

 

One of the columns is for a Job Number. Jobs can vary from 20 pieces to sometimes 1000 pieces.

 

Would it be better practice to create a table that stores a Job Number ID with the job number and then refernce the ID to the test data of each piece? Our job number are 7 digits long by the way.

 

Thanks

[bri189a]

I am creating tables for a test program I am writing that stores the test data.

 

One of the columns is for a Job Number. Jobs can vary from 20 pieces to sometimes 1000 pieces.

 

Would it be better practice to create a table that stores a Job Number ID with the job number and then refernce the ID to the test data of each piece? Our job number are 7 digits long by the way.

 

Thanks

Don't know if it's the best way but this is how I typically handle those kind of situation:

 

MainTable:

ID = AutoIncrement

OtherFields

 

JobToJobNumbers

FK_Main int

FK_Job int

 

Jobs

ID = AutoIncrement

JobDescription = string

 

Then the job itselft only has one record, regarless of how many jobs are in it. You set up a relation ship:

 

MainTable.ID -> JobToJobNumbers.FK_Main

-and-

JobToJobNumbers.FK_Job -> Jobs.ID

 

This kind of set up with a strongly written JOIN will allow one job to pull up 1 to infant amount of 'pieces'.