MySQL Database Clarification

Question

I am new to Database Design and I really need some help figuring this one out.

I have a table called Processes, this table holds all the names of the job processes. I then have a table called TrainingProcesses. This table simply has the names Process1, Process2, Process3 and so on until Process30.

Each employee can train up to 30 processes. So for an employees first process he is learning, I need to be able to select a process ID, assign that process ID to Process1 in his profile. Process1 is not anything specific, it's just going to be whatever job process they assign to him.

Can you tell me how I would link all this together?

• EmployeeInformationTable

  • `Id``
  • `FirstName"
  • LastName

• ProcessesTable

  • Id
  • Appm
  • Route H
  • RouteJ

• TrainingProcessesTable

  • Process1
  • Process2
  • Process3
  • EmployeeInformationTable Id
  • ProcessTable Id

Would it be done like above in simple terms?

Answer 1

You do not want to store the processes as separate columns. This just causes headaches when you want to do something like "add a new process after all the other ones".

• EmployeeInformationTableId
• ProcessTableId

And you probably want more information as well:

• Date/time completed
• Completion metric (time, quality)
• Sequential number
• etc.

How do you restrict the length to 30 per employee? Unfortunately, this is a bit tricky:

1. One method is just to do it in the application layer (count the number of existing rows before adding a new one).
2. A second method is to wrap all DML operations in stored procedures, and write the logic there.
3. A third method is to use triggers to prevent the operation.

In practice (1) is easiest; (2) is often what I do anyway, and (3) is perhaps the safest, so the database maintains the integrity.

Answer 2

Your table structure as it is works, and achieves your desired goals. So could it be done like the above simple terms? Yes.

You may also want to consider removing the "process1-30" columns from the TrainingProcessTable, and just limiting the max number of processes an employee can train up to inside of the logic of your application instead of the database. The benefit for this would be that if you needed to increase the number of processes an employee can train up to you will not have to alter your database schema.

(EmployeeInformationTable) "Id" "FirstName" "LastName"

(ProcessesTable) "Id" "Appm" "Route H" "RouteJ"

(TrainingProcessesTable) "EmployeeInformationTable Id" "ProcessTable Id"