Re: SQL Database design question

You prevent duplication in the EmployeeBranches table as in any other table: with a PK or UNIQUE constraint. However, your constraints have to allow an employee to be added without a corresponding entry in EmployeeBranches, otherwise you could never add new rows to the Employees table.

CREATE TABLE Employees (ee_code CHAR(10) PRIMARY KEY, ee_name VARCHAR(50) NOT NULL) CREATE TABLE Branches (branch_id INTEGER PRIMARY KEY, branch_name VARCHAR(50) NOT NULL UNIQUE) CREATE TABLE EmployeeBranches (ee_code CHAR(10) NOT NULL REFERENCES Employees (ee_code), branch_id INTEGER NOT NULL REFERENCES Branches (branch_id), PRIMARY KEY (ee_code, branch_id))

-- 
David Portas
SQL Server MVP
--