Clever Geek Handbook

Database schema

I have 2 Office and Employee objects and I want to create a database schema for WorkHours. There are offices that have default working days for their employees, but there are Employees who may have different working hours.

What's the best way to model it? Do I have business hours in both tables?

+3


source to share


4 answers


What you can do is create a diagram similar to the one below. Of course, you need to add additional columns to store additional data if you have one, and also set up queries with data types specific to the RDBMS being used.

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, be sure to implement constraints and primary keys on your unique columns in each of your tables.

In the table, Employee

you add a column to indicate if Employee is running during the default office hours ( UseOfficeDefaultWorkingHours

).



In the table, WorkingHours

you add a column to indicate if the row contains the default office hours by using another boolean column, in this case OfficeDefaultWorkingHours

.

You can request this diagram to get working hours for an employee with a request similar to the following:

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 in SQL Server RDBMS, but I'm not sure if it will work with other RDBMS products and you might need to tweak accordingly.

+3


source


you can do one thing: create a new WorkingHours table as employee independent

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
+2


source


WorkingHours will be a different entity.

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

0


source


Create three tables
    1. Office: this should be an office location, name and own ID
    2. Synchronization table: ID, OfficeID, TimeSLot
    3. Employee. In this table, create an OfficeID column and pass the office id from the office table it works in and another column with TimeslotID and pass id of the second table.

For example: EmpID, OfficeID, TimeSLotID

0


source






More articles:


All Articles