Where Should I put data related to more than one entity?

Question

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 ?

Answer 1

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.

Answer 2

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

Id, 
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.