SQL Database design question

From: Dan Williams <dtwilliams_at_hotmail.com>
Date: Fri, 28 Jan 2005 09:49:40 +0000 (UTC)
Message-ID: <ctd1rk$4fl$1_at_sparta.btinternet.com>

I am using Microsoft SQL Server 2000 and have a question about our database design.

Here is a sublist of tables and columns we currently have:-

Employee

---

Ee_Code PRIMARY KEY
Ee_Name NOT NULL

Branch

---

Branch_ID PRIMARY KEY
Branch_Name NOT NULL

Is it better to create a new EmployeeBranch table that contains a list of employees and the relevant branches they can work at (as they should be able to work at more than one branch), or is it better to create several columns in the Employee table that correspond to the branches they can work at.

For example,

EmployeeBranch

---

Ee_Code
Branch_ID

or

Employee

---

Ee_Code
Ee_Name
Ee_Branch1
Ee_Branch2
Ee_Branch3, etc...

To me it obviously appears better to use my first suggestion. But, how do i go about ensuring that each employee has at least one entry in the EmployeeBranch table, and that each employee can only have one occurrence of each individual branch (ie. there's no duplication of EmployeeBranch data)?

Is it possible to setup constraints and relationships on our tables to allow for this and how do i go about doing it?

Thanks in advance for any suggestions

Dan Received on Fri Jan 28 2005 - 10:49:40 CET