What are the best practices for structuring a MySQL database to store shift data for a work schedule?

When structuring a MySQL database to store shift data for a work schedule, it is important to create tables for employees, shifts, and a mapping table to link employees to shifts. This allows for easy retrieval of data and efficient querying for scheduling purposes. 

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE shifts (
    shift_id INT PRIMARY KEY,
    start_time DATETIME,
    end_time DATETIME
);

CREATE TABLE employee_shifts (
    employee_id INT,
    shift_id INT,
    PRIMARY KEY (employee_id, shift_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (shift_id) REFERENCES shifts(shift_id)
);

Keywords

MySQL database structure work schedule shift data PHP

Related Questions