What are the potential pitfalls of storing vacation days in a single column in a database?

Storing vacation days in a single column in a database can make it difficult to track and manage each individual vacation day. It can also limit the flexibility in calculating and updating vacation days for employees. A better approach would be to store each vacation day as a separate record in a separate table, linked to the employee's ID. 

// Employee table structure
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

// Vacation days table structure
CREATE TABLE vacation_days (
    id INT PRIMARY KEY,
    employee_id INT,
    vacation_date DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

// Example query to insert a vacation day for an employee
INSERT INTO vacation_days (employee_id, vacation_date) VALUES (1, '2023-01-01');

Keywords

database design normalization data integrity SQL queries date functions

Related Questions