Right Now I design Time & Attendance System.

If I have the following entities :

  • Shift (Id,Name,start_time,end_time, ...etc)

EX :1,Morning,08:00,16:00

  • WorkSchedule (Id,Name,Type,...etc)

EX :1,General,1

Where the Type may be(Traditional,Daily Flexible Hours ,Weekly Flexible Hours)

And WorkSchedule has detail using Shift like this :

Day   Shift      Weekend

Sa       1           1
Su       1           0
Mo       1           0
Tu       1           0
We       1           0
Th       1           0
Fr       1           1

Now If i want to add the Core Hours for the flexible type ,Where should i specify the start time and the end time for the Core Hours? in Shift Entity or in WorkSchedule Entity or something else ?


One way of modelling this would be to create FlexibleWorkSchedule type which derives from WorkSchedule and put the core hours in that.

There are already plenty of resources online that explain how to model inheritance in a relational database. One way that I've seen involves adding a table for each sub-type and every instance of your subtype then has a record in the subtype table and the supertype table with matching primary key.

  • Then you say that the start_time and end_time of the core hours have to belong to the WorkSchedule Not Attendance – Anyname Donotcare Feb 6 '17 at 13:52
  • I'm saying they belong to a specific subtype of WorkSchedule. – MetaFight Feb 6 '17 at 14:01
  • then it will be in the details so every day of the week will have (start_time,end_time) for core hours although the timing should be in Attendance ? – Anyname Donotcare Feb 6 '17 at 14:04
  • to be more precise, child table's primary key is also a foreign key to the parent's primary key. – Walfrat Feb 8 '17 at 9:26

I agree with @MetaFight.

However, you are perhaps having some confusion that might go to a discussion of concepts and relationships, and what varies with what.

The notion of core hours is that it is something published/announced by management in advance and that they (the core hours) are fixed/constant across employees (in the same category). As such, the core hours do not vary with an individual employee's attendance.

An individual's attendance can be graded/rated against the published core hours, which is a separate matter (from the existence of core hours).

So, to answer your question, we would ask ourselves, what do the core hours vary with? Attendance or with WorkSchedule (assuming those are the only two choices).

And the answer is certainly not Attendance. So (assuming we have to choose from the two), it must be WorkSchedule.

Now as @MetaFight is pointing out, not all WorkSchedule types are necessarily the same. Some have fixed hours, others have core hours. There are also many other potential properties of different WorkSchedules. For example, the ones that have core hours also probably have a total hour count as well, since the core hours are usually shorter than the expected work hours for one day.

@MetaFight is suggesting that you model the different WorkSchedule's as subclasses and map/apply that to the database modeling.

Another possibility is that you model core hours and total hour count for both fixed and flexible WorkSchedule types, which would tend to unify the concepts.

There would be only core-hours-start, core-hours-end and total day's hour count, (we could also model this as core-hours-start, core-hours-duration, additional-hours-expected.)

For fixed schedules, the core hours add up to the total work day's count, and so the core hours start is just the regular start and the core hours end is just the regular end. The count is simply end less start, (or additional-hours-expected is 0) as there are no extra hours expected by an employee on fixed schedule.

For flexible schedules, the core hours don't add up to the total work day's count, so the total expected is still, perhaps, 8, or in other words, additional hours are expected.

I would put the hours in the shift structure, something like

min start day, min start time, 
max start day, max start time, 
core hours count,
flex hours count

The schedule composes with a set of shifts.

This will allow for both fixed and flex shifts, as well as both day and night shifts.

  • Thanks a lot , but I want to illustrate that i create firstly the Attendance which is start_time(attend),end_time(leave) (Fixed) then i use this in the WorkSchedule i mean that WorkSchedule is a week, every day in this week has an Attendance ,so if i follow your recommendation what should i do ? I will be grateful if you could add a graph to illustrate it .(I'm talking about the analysis phase no implementation yet) – Anyname Donotcare Feb 7 '17 at 8:24
  • @AnynameDonotcare, You seem to be using either the word Attendance or Schedule in an unfamiliar way. You might search for a better term for one or the other regarding what your trying to describe. – Erik Eidt Feb 7 '17 at 16:47
  • yeah i mean by attendance the entry time and the exit time , and the schedule is composed of attendance for every day in the week. – Anyname Donotcare Feb 7 '17 at 17:14
  • excuse me my main language is n't English, according to my explanation to what i meant , could you help me regarding the first comment 🙏🏻 – Anyname Donotcare Feb 7 '17 at 17:26
  • 1
    OK, very good. I will append some thoughts to my answer shortly. – Erik Eidt Feb 8 '17 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.