CODEWITHSUNDEEP
mysqlone-to-many
John T
John T

Reputation: 1078

How to design this one to many database?

I've been doing some reading on 'one to many' databases but I'm struggling understand the best way to implement a solution in my case.

I want a MySQL database to record which employees have read certain training hand-outs at work.

So I have a table of Employees. And I have a table of Hand-outs.

I want to record if the Employee has clicked to say they've read the hand-out.

My Employee table has ID, Name, Email.

My Hand-out table has ID, Title

Am I better adding a field to the Employee table that will contain a list of "Hand-out IDs" to show which hand-outs they've read?

I need to be able to easily search to show what percentage of Employees have read a particular hand-out and I think my method would make that very difficult?

I can't have separate fields on the Employee table such as Handout1, Handout2 etc as new hand-outs will be added regularly.

I'm sure this must be a common problem so wondered if someone could direct me to the best solution for this?

Thanks

Upvotes: 0

Views: 39

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

I think you need a bridge table here which records relationships between employee and hand-out records. Something like this:

Employee_Handout (ID, EmployeeID, HandoutID)

Every time a new handout comes out, you would insert a record into the handout table. Then, when a given employee reads that handout, you would insert a new record into the Employee_Handout table. You probably don't need to persist non-reads, since they could easily be detected as being absent from the bridge table.

The primary key for this column would probably be (EmployeeID, HandoutID), assuming you would only want a single record relating an employee reading a given handout. This would also mean that a given employee/handout relationship could only be persisted once.

Upvotes: 1

Related Questions