Entity Relationship Question

[csolomon]

Hello:

I am building something to keep track of vacation time per department per employee.

The way we keep track of it is a department will get 4 hours every 30 days they go with out an incident. If any one employee has an incident with in this 30 days, all of the employees in the department lose the 4 hours for the current month, as well as the following month.

I am wondering who the relationship belongs to:
A department earns vacation time
or
An employee earns vacation time

I need to keep track of how much vacation each employee has incurred.
I would appreciate any input.

[Stewart Ross]

Hi. Since the gain or loss of entitlement is at employee level it should be clear that the entity involved is employee, with the dept grouping involved in implementing the business rule for all employees if any one transgresses.

The employees are entitled to the leave, not the dept. As applied in this case a dept is an administrative grouping which links a subset of the company's employees to a specific business unit. The business rule you quote just appears to mean that if an incident occurs the employees in that grouping lose their entitlement.

-Stewart

[beacon]

I'm bad at relationships (haha, I made a pun), but here's my two cents:

If an employee can only belong to one department and everyone in the department earns vacation time when there isn't an incident, it would seem to me that there would be a 1:many relationship between Department and Employee and another 1:many relationship between Employee and Vacation time.

After setting up the tables, you could query the amount of time for each employee or for the department (of employees) as a whole.

Does that make sense?

[csolomon]

Yes, that makes sense.

(I often find it helpful to talk out the relationship problem before I decide on what the relationship is)

Thanks to you both!

[beacon]

I'm glad you did...it helps me out too. I like to think of it as mind aerobics!