In many database designs, you encounter situations where a many-to-many relationship exists between two entities. For example, an employee can be assigned to multiple projects, and each project can involve multiple employees. To represent this, you use an associative table (sometimes called a join table or bridge table), which connects the two entities by storing pairs of their primary keys. However, sometimes you need to store additional information about the relationship itself—such as the role an employee has in a project, the date they were assigned, or their allocation percentage. In these cases, attributes are added directly to the associative table, enriching the relationship with more context and detail.

-- Associative table linking employees and projects, with an additional 'role' attribute
CREATE TABLE project_assignments (
    employee_id INTEGER NOT NULL REFERENCES employees(employee_id),
    project_id INTEGER NOT NULL REFERENCES projects(project_id),
    role VARCHAR(50) NOT NULL,
    assigned_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (employee_id, project_id)
);

-- Sample data: assigning employees to projects with roles
INSERT INTO project_assignments (employee_id, project_id, role, assigned_date) VALUES
(1, 1, 'Project Manager', '2024-05-01'),
(2, 1, 'Developer', '2024-05-10'),
(3, 3, 'Support Specialist', '2024-06-01'),
(5, 2, 'HR Assistant', '2024-06-05');

When you add attributes to an associative table, you can easily query and update these attributes to gain insight into the relationship. For example, you might want to find out which employees are assigned to a particular project and what their roles are, or you may need to update an employee's role for a specific project. The associative table structure makes this flexible and efficient, as all relationship-specific data is stored in one place, separate from the core entity tables.

-- Query: Find all employees assigned to the 'Website Redesign' project along with their roles
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    pa.role,
    pa.assigned_date
FROM
    project_assignments pa
    JOIN employees e ON pa.employee_id = e.employee_id
    JOIN projects p ON pa.project_id = p.project_id
WHERE
    p.name = 'Website Redesign';

Adding attributes to an associative table is a powerful pattern for modeling rich, real-world relationships between entities. It allows your database to answer questions not just about who is related to what, but how and in what capacity—all while keeping your data normalized and maintainable.