0

I am trying to develop a database to store data collected by a web based application that tracks employee activities. I have an employee table that looks like this

Employee
--------
id
name
position
email

And and multiple activity tables, each with different columns. An example of one:

OutreachAndTraining
-----
id
date
county
city
type
...

ProfessionalDevelopment
------
id
date
comments

I want to be able to keep track of which employees were a part of each activity but only want to log the activity itself once. What is the best way to associate multiple employees with one activity? Will I have to create a separate table for each activity to store employee IDs and activity IDs?

jason
  • 5
  • 1

1 Answers1

1

Assuming an employee can take part in more than one activity, what you have here is an N:M relationship between employees and activities, which is modeled with the additional "link" table per each relationship:

enter image description here

If you have many kinds of activities, you may consider inheritance (aka. category, generalization, subtype or class hierarchy), to minimize the number of relationships (and therefore "link" tables):

enter image description here

For more information on inheritance, search for "Subtype Relationships" in ERwin Methods Guide.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Many thanks for this example. I was having a hard time visualizing the link tables and was wondering if that was the only possible solution. Looks like subtypes might be the best way to go. – jason Apr 11 '12 at 14:06
  • Ok, this all makes sense to me as far as retrieving entries from the database but I am still having trouble determining how to go about entering this info and what the SQL query might look like. – jason Apr 12 '12 at 20:50
  • ... to log an activity for multiple employees. I am autoincrementing my IDs for the activity tables so how will I know what to insert to the EmployeeActivity table for ActivityID before the activity has even been inserted? – jason Apr 12 '12 at 20:58
  • @jasontk19 Obviously, you need to discover what `id` was generated when row was inserted in `Activity`, before you can insert it in `EmployeeActivity.ActivityId`. This is done either by separating the `id` generation from the insertion (on DBMSes that support sequences: generate new `id` from the sequence, insert it into `Activity`, then insert the _same_ `id` into `EmployeeActivity`) or by returning the newly generated `id` directly from the INSERT statement, typically through something like `INSERT ... RETURNING ...` or `INSERT ... OUTPUT ...`. – Branko Dimitrijevic Apr 12 '12 at 21:14
  • @jasontk19 Less commonly, you could even choose the type for `id` such that it can be uniquely generated in advance by the client (e.g. GUID). This has its pros (easy and portable) and cons (more storage-hungry than plain int, index fragmentation). – Branko Dimitrijevic Apr 12 '12 at 21:16
  • Did some looking around and found that MySQL has a `LAST_INSERT_ID()`command that does exactly what I need. Thanks again! – jason Apr 13 '12 at 14:21