0

Let's say I have Employee & Meeting tables. 

Employee
-----------
id
name


Meeting
-----------
id
name


Employee_Meeting
-----------
id
employee_id
meeting_id

Now I need to add employee's tasks (or anything else) in that meeting. What is a best practice to do that? I could have a relationships like this:

Employee_Meeting_Task
-----------
Employee_Meeting_id
task_id

or

Employee_Tasks
-----------
id
employee_id
meeting_id
task_id

Are both these approaches valid from 'proper' database design point of view?

My concerns are:

Case 1: Most of the time if I remove employee from the meeting I need to remove his tasks in that meeting as well. Employee_Meeting_Task would enforced that by constraint.

Case 2: Sometimes employee participates some daily meeting and regularly does same tasks in that meeting. If he got sick for several days, I would like to remove him from the meeting, but I don't want to re-add his tasks to the meeting next time he participates it.

jonasnas
  • 3,540
  • 1
  • 23
  • 32
  • Since an employee can have more than one task in a meeting, I'd say having an Employee_Meeting_Task table makes the most sense. If it was a 1 to 1 mapping, the table would feel redundant though. – Tobberoth Dec 10 '13 at 13:07
  • 1
    Read http://stackoverflow.com/questions/2190272/sql-many-to-many-table-primary-key on primary keys for many-to-many tables. – Neville Kuyt Dec 10 '13 at 13:43

1 Answers1

1

There are a couple of things you should look at. First, if it's possible for an employee to have a task that is not associated with a meeting, you might be in trouble. One way out of it is to have a record in the meeting table with a name like "not applicable". Another way is to take the meeting_id field out of the employee_tasks table. The best way depends on the requirement to associate tasks with meetings, which is something you have to ask your taskmaster about.

Next, for many to many tables, you don't need a separate id field. For example, for employee_meeting, the PK could be the employee_id and meeting_id.

Next, look at what you said for Case 2. If there is a daily meeting, would that not be one record in your meeting table for each day?

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • For simplicity let's say that all meetings are daily and have one record per meeting in meetings database. The doubt I have is how properly design tables when sometimes you want to remove record from 'many to many' table with all related data and sometimes you want to remove only temporarily the mapping but keep related data. I use Meeting/Employee just as an example. Now I think I could add 'is_active' in the employee_meeting table to know if employee was removed only temporarily and use Employee_Meeting_Task to specify tasks. Is it better to use foreign key for 2 columns rather than single? – jonasnas Dec 10 '13 at 13:41
  • I think you are better off with start and stop dates in the employee_meeting table. That allows you to keep a history. – Dan Bracuk Dec 10 '13 at 13:56