Database Many To Many Relationship

[RockLobster]

I don't know why but this is driving me crazy ffs. I'm storing what days members of staff have off (absences).

Two tables, Days and Person.

Person
-------
Person ID
Name


Days
------
Day ID
Date


Is that a many to many relationship? I'm reading it like this, many days can be taken off by one member of staff and many members of staff can take one day off. Always confuse me many to many.

 

[RockLobster]

Ok so I have:-

Person Table
------------
PersonID
Name
etc

Day Absence
-------------
Date
PersonID

One to many between them. Ok if a member of staff has one day off it is one absence instance, if they have three weeks off that is one absence instance. What would suggest to record the absence instances? Have another table called AbsenceInstance and have a many to one. Many days are associated with one absence instance and vice versa. We need to record absence instances because if a member of staff has three in three months they have a stage one review, five in 6 months stage 2 review, etc.

 

[RockLobster]

If it's the number of absences rather than the total number of days that you want to count then that's the way to go.

We need to know both but I can work out the number of days from the start and end date of each instance, can I? I'm trying to work out how I would write this in my app. For example say I have a 'Absence Today' button when a user clicks on it, I will need some code to check the absence instance table to see if there's an instance for yesterday if there is adjust the end date to today if there's not add a new instance. What do you think?

 

[RockLobster]

If everyone always works only on weekdays say, then it's easy to work out the number of days from the start and end dates.
If people work different hours/days then you'd need to either explicitly specify the days they were absent or store their working times and derive it individually that way.

As for your absence today button, I would simply have someone mark someone as absent as soon as they are off and put a null value in the return date field, then when they return to work their supervisor or whoever would update the record to show when they returned.

Cheers, good points. I forgot about the people working different days, we have some staff for e.g. they might only work Mon to Wed every week. no one works weekends. What do you think have a field for each day of the week?