Company-Employee database design

Question

I am trying to build a SQL 2005 database that has a Company data table and an Employee data table. Each company should have an Employee 1, Employee 2, and so on. I realize that each Employee record would have a PK of both their EmployeeID and their CompanyID, but I haven't for the life of me determined the best way of doing this?

Any ideas?

Let me me clarify this a bit

Each company can have its own employee #1, #2, etc. Say I have a stored procedure that returns a specific employee by passing in the company id and the requested employee id.

exec dbo.GetEmployee @CompanyID = 1, @EmployeeID = 45

Keeping in mind that Company 1 can have an Employee 1 and Company 2 can have a different Employee 1, how can I do this? How can I have an incremental primary key for employees unique for each company?

Answer 1

Sounds like there's a foreign key in there somewhere. It's a 1:many relationship between Employee and Company, assuming that an Employee could only work for one Company at a time. Both Employee and Company would have a PK, and Employee would have an FK that references the Company PK.

I'd urge you to use surrogate keys for both Company and Employee. Keeping business logic out of keys is a good idea.

Answer 2

If your question is a data modeling question:

If Employees can only work for one Company, then you could have the CompanyID just be a foreign key reference in the Employee table, and EmployeeID be something unique to any given person (like SSID for example).

If Employees can work for more than one Company, then you have a many-to-many relationship, which would require a third table -- named something like CompanyEmployee, consisting of CompanyID and EmployeeID.

That help?

OR, is your question simply:

"How do I do a compound primary key in SQL Server"?

Answer 3

I would have a table called Company with a Primary Key column CompanyID and then I'd have a table called Employee with a Primary Key column of EmployeeID and a Foreign Key column of CompanyID. Of course you would have EmployeeName and CompanyName and any other appropriate columns on each table.

Answer 4

Based on you comment to Jamo, you need two employee IDs. One that is unique by employer and one that is unique throughout the database. Then you have these tables:

Employees
E_ID NUMBER(9) NOT NULL
E_NAME
E_SSID
etc.

Companies
C_ID NUMBER(9) NOT NULL
C_NAME
etc.

Employment
CE_ID NUMBER(9) NOT NULL
C_ID NUMBER(9) NOT NULL FOREIGN KEY (Companies.C_ID)
E_ID NUMBER(9) NOT NULL FOREIGN KEY (Employees.E_ID)
LOCAL_E_ID NUMBER(9) NOT NULL

C_ID is a unique identifier for a company. E_ID is a unique identifier for an employee in the database. CE_ID is a unique identifier for the employment table to make it easier to do updates. LOCAL_E_ID is unique by company. You will need to create some kind of trigger to create this number.

Answer 5

The simple answer is you can't.

You create a primary key on the employee table, over the company ID and the employee ID. That takes care of the unique constraint.

However you cannot have the employee ID increment automatically, instead in the insert you need to wrap it in a transaction and do something like

insert @companyID, max(employeeID)+1 from employees where companyID=@companyID into employee

Answer 6

The short answer is that you probably are barking up the wrong tree.

If the employeeID is assigned as an autoincrementing (and thus arbitrary) number, there is no reason to restart the employeeID for each company. If you think I am mistaken, please provide an example and I'll adjust my answer.

This design is just going to create an unnecessarily complex design. For example your stored procedure really ought to take a single EmployeeID and that employee record would then be used to determine the company.