I have a problem with a relationship between two entities and I am not quite sure how to model it. Here is what is happening:

A person can call in many incidents. An employee (dispatcher) receives many incidents. That incident is assigned to a different employee (officer) for resolution.

At minimum, I think I am missing a recursive relationship for the employee entity.

I must mention that this is a homework assignment and I would like you to provide me the answers. (Please delete this post afterwards so my teacher doesn't expell me)

Boy, you guys are hard on these kids. I had to laugh though after reading a few of the recent posts of these kids looking for the answers.

There are lots of reasons to use recursive relationships in an employee table, but none of them apply to the requirements you list in your post.
You need a one-to-many relationship between people and incidents, and you should just store the dispatcher and officer IDs in the incident table, assuming each incident can have only one of each.

Blindman, thanks. A couple of questions.

The relationship that travels up off the top of the image is actually connected to the "person" entity. The child "incident" entity is the other end of that relationship as pictured.

I assume when you say that I should have a "relationship between people and incidents" that was what you were saying?

With regard to the "rel_Incident_Company_Employee" entity, this entity resolves a M:M relationship between the incident and employee entities. Basically, many employees resolve many incidents and many incidents are either assigned or taken by many employees.

Under the rel entity I have an officer_Dispatch entity with nothing more than a timestamp. Can I add the timestamp into the rel entity? Thanks.

A wee bit off topic, but what do you use to draw your pretty ER diagrams?
And welcome back Frank

Its good practice to add timestamps to ALL your tables. Along with a column that records the lost login to modify the record.

You have many-to-many relationships between incidents and dispatchers, and incidents and officers? That doesn't make a lot of sense to me (several people can dispatch the same incident? several people can resolve the same incident? Ok...), but if this is the case then you have two options:
1) Store incident dispatchers and incident officers in the same table, with a column indicating their role.
2) Use separate tables for incident dispatchers and incident officers.
I don't see you implementing either of these in the schema you posted.

Blindman, thanks again for the help. Your right, it doesn't make a whole lot of sense that several people can dispatch the same incident.

Actually, only one person can dispatch a single incident but (and here is where I'm getting screwed up) an officer IS also an employee so technically speaking, a single dispatcher takes the incident and either a single or multiple officers respond to the incident. This is how I am arriving at a M:M relationship between the incident and employee entities. I'm sure this is incorrect. How do I model this? Would I still use your suggestion to create a single table for dispatchers and officers?

I had originally modeled two entities; one for dispatchers and another for officers but after rethinking it I didn't think that was correct. Both are technically employees.

Why, Microshaft Wurd of course.

Thanks for the WB. Its nice to be back. I stayed away for a while because I needed "healing time" and needed to see a shrink after Rudy's last tounge lashing. (I still have nightmares though) Just kidding Rudy.

Incident to Dispatcher is a one-to-one relationship, so store the Dispatcher in the Incident table.
Store the Officers that respond to the Incidents in a separate table to implement the many-to-many relationship.

Ok, thanks Blindman. I appriciate the help. Also, thanks for the advice on the timestamp to record the modifications made.

Blindman, just to satisfy my curosity before I move the dispatcher to the incident table as you suggested, would this model be better? Would it work?

Basically, the employee (dispatcher) processes the incident. The incident is dispatched to employee (officer). The employee (officer) responds to the incident. What do you think?

I don't like the look of the relationships between

• incident
• company_employee
• rel_Incident_Company_Employee

I'm assuming the latter table is in there to break down your many-many relationship...
So in theory if you remove that you have a M:M relationship, therefore you would have 2 different joins between the tables... which is it to be?

Many incidents -> Many employees
OR
One Employee Processes Many incidents