You have a table of employees and their departments. You need to ensure that no employee can belong to more than one department at the same time. How would you enforce this constraint in the database?

To ensure that an employee cannot belong to more than one department at the same time, we need to enforce a uniqueness constraint on the employee_id column in the employees_departments table.

Approaches to Enforce This Constraint

1. Using a UNIQUE Constraint (Best for One-to-One Mapping)
If an employee can belong to only one department, we can enforce it with a UNIQUE constraint on employee_id.

SQL Implementation

CREATE TABLE employees_departments (
    employee_id INT PRIMARY KEY,
    department_id INT NOT NULL,
    CONSTRAINT unique_employee_department UNIQUE (employee_id)
);

This ensures that each employee_id can only appear once, preventing multiple department assignments.

Pros:

• Enforces uniqueness at the database level.
• No additional query overhead.

Cons:

• Does not support many-to-many relationships.

2. Using a Composite Primary Key (For Many-to-Many Relationships)
If employees can change departments but must belong to only one at a time, we can track changes with a composite key.

SQL Implementation

CREATE TABLE employees_departments (
    employee_id INT,
    department_id INT,
    PRIMARY KEY (employee_id, department_id)
);

This allows an employee to be in one department at a time but prevents duplicate (employee_id, department_id) pairs.

Pros:

• Supports historical tracking if combined with an effective_date column.

Cons:

• Requires manual deletion of old department records when reassigning employees.

3. Using a Trigger to Enforce Business Logic
If the business rule requires additional validation, we can use a trigger to reject duplicate assignments.

SQL Implementation (PostgreSQL Example)

CREATE OR REPLACE FUNCTION check_department_assignment()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM employees_departments WHERE employee_id = NEW.employee_id) > 0 THEN
        RAISE EXCEPTION 'Employee already assigned to a department!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_single_department
BEFORE INSERT ON employees_departments
FOR EACH ROW EXECUTE FUNCTION check_department_assignment();

This prevents an employee from being assigned to multiple departments by raising an error.

Pros:

• Can be customized for specific business rules.

Cons:

• Triggers can add performance overhead for large-scale inserts.

Nuances to Consider

• Using Foreign Keys
  • Ensure department_id references a valid department.
• Handling Department Transfers
  • If employees can switch departments, use DELETE before inserting a new department.
• Performance Considerations
  • Triggers may introduce slight performance overhead in high-concurrency systems.

Tecyfy Takeaway
The best way to prevent employees from being in multiple departments depends on the business model.

• Use a UNIQUE constraint for strict one-to-one enforcement.
• Use a composite primary key for many-to-many relationships.
• Use a trigger for custom validation logic when enforcing business rules dynamically.
  Choosing the right method ensures data integrity and efficient enforcement of constraints.