Database schema

Question

I have got 2 entities Office & Employee and I want to design the schema of the database for WorkingHours. There are offices that that have default WorkingHours for their employees, but there are Employees that might have different WorkingHours.

What is the best way to model that? Do I Have WorkingHours in both Tables?

Answer 1

What you can do is create a schema similar to the one below. Of course, you need to add additional columns to hold additional data if you have any and also adjust the queries with datatypes specific to the RDBMS you're using.

CREATE TABLE Office(OfficeID integer
                  , OfficeName VARCHAR(10))

CREATE TABLE Employee(EmployeeID integer
                    , EmployeeName VARCHAR(10)
                    , OfficeID integer
                    , WorkingHoursID integer
                    , UseOfficeDefaultWorkingHours Boolean)

CREATE TABLE WorkingHours(ID integer
                        , StartTime TIME
                        , EndTime TIME
                        , OfficeID integer
                        , OfficeDefaultWorkingHours Boolean)

Also, don't forget to implement constraints and primary keys on your unique columns, in each of your tables.

In the Employee table you add a column to specify if the Employee is working under Default Office working hours (UseOfficeDefaultWorkingHours).

In the WorkingHours table you add a column to specify if the row contains the default working hours for the office with the help of another Boolean column, in this case OfficeDefaultWorkingHours.

You can query this schema to get the working hours for an employee with a query similar to the one below:

SELECT
    E.EmployeeName
    , W.StartTime
    , W.EndTime
FROM Employee E
    INNER JOIN WorkingHours W ON E.OfficeID = WorkingHours.OfficeID
        AND E.UseOfficeDefaultWorkingHours = W.OfficeDefaultWorkingHours
        AND W.ID = CASE 
                        WHEN E.WorkingHoursID IS NOT NULL
                            THEN E.WorkingHoursID
                        ELSE W.ID
                    END
WHERE E.EmployeeID = 1

This query will work under a SQL Server RDBMS, but I am not sure if it will work on other RDBMS products and you might need to adjust accordingly.

Answer 2

WorkingHours would be a different entity.

An office has one default working hour (or maybe more than one?). An employee has one working hour. The employee's working hour data is taken from the employee's office's working hour. The employee's working hour can change without changing the office's working hour.

Answer 3

you can do one thing is create new table WorkingHours as its independent from the employee

Working hours 

Id  Working Hours
1     10 - 8
2     12- 9

assign id value to employee table

Employee

ID WorkingHoursID
1     2
2     2
3     1

Answer 4

Create Three Tables
1. Office: This should be office location, name and Its own ID
2. Timing Table: ID, OfficeID, TimeSLot
3. Employee: In this table create a Column OfficeID and pass ID of office from office table in which he or she is working and Other column with TimeslotID and pass ID of second table.

Like : EmpID, OfficeID,TimeSLotID