Please help with this DB design... (1 Viewer)

[Steepleboy04]

I want to create a database that stores a customer's history of problems with a specific product on a particular machine. I think I have correctly created all the tables I need with their respective primary key.

Please note the TBLJobRegister table at the bottom of this post. This was going to be the table that links all these tables together. Is this design incorrect?

Here are my current tables and their fields:

TBLCustomer
-CustomerCode (primary key), name, address, city, state, country, phone no

TBLProblem
-problemID(primary key), description, comment

TBLSolution
-solutionID(primary key), description, comment

TBLMachine
-MachineID(primary key), description, comment

TBLProduct
-ProductID(primary key), product type, description

TBLMachineOperator
-OperatorID(primary key), firstname, lastname

TBLJobRegister
{fk = foreign key}
-JobID(primary key), Date, CustomerCode(fk), MachineID(fk), OperatorID(fk), ProductID(fk), ProblemID(fk), SolutionID(fk)

Can I have this many foreign keys in one table???

 

[Steve R.]

The Primary Key should be limited to linking the various tables together. As such it would be a "hidden" value that the user never sees. If should not be used as a PartNumberID, StudentID, ActionID, ProjectID, etc.

You can have as many Foreign IDs as you want.

 

[Steepleboy04]

So I can I create another field and use an autonumber data type, and make this new field my 'hidden' primary key?????

I want to be able label records by an 'ID' field that distinguishes them from all other records. Why wouldn't the 'ID' field become the primary key in this respect?

 

[Steve R.]

This is a very subtle question, you are correct to imply that this numbering approach has the effect of creating two sets of unique numbers. Clearly it is easy to say that this approach would be redundant.

Nevertheless, I would advocate this approach. The reason has to do with the fact that part numbers, student id numbers, etc. can change and also be in unique formats. These changes can be in various forms such as reformating the numbers and deleteing items. Hidding the auto generated key number keeps management simplier.

For example, on the database that I manage, the project number consists of the year and an incremental number (20060001, 20060002, etc.) This assignment is by VBA code. So it is also automatic. The AutoNumber key field is used to link the core table to all the other tables. Should I decide to change the format of the project numbers, it won't affect any of the related tables.

Its your database, experiment.