Database implementation

Question

I have some trouble converting some specifications to sql tables :

The scenario is as follow :

an employee (there is already an EMPLOYEE table) can have one or several lists of employees, and an employee can belong to one or more of these lists.

Example : employee_1 has 2 lists, list_1_1 and list_1_2.

list_1_1 holds employee_2, employee_3 and employee_4.

list_1_2 holds employee_3 and employee_5.

employee_1 can in turn belong to a list of another employee.

I first thought of a table with these columns :

list_name;owner_id;employee_id

but then, what would the primary key of that table be ? I am sure there is a better way to achieve this, but I'm stuck.

You can try OwnerID and ListId as PK also.. – K T Sep 24 '13 at 10:04 — K T, Sep 24 '13 at 10:04

K T · Answer 1 · 2013-09-24T10:54:24.017

0

There can be 1 table emp: Emp Id | Emp Name...etc with EmpID as PK

Then Emp_List_Map table: EmpID | ListId with (EmpId and ListId) as PK

And then List Table: ListId | EmpID with (ListId and EmpID) as PK

This is what I think..

edited Sep 24 '13 at 10:54

answered Sep 24 '13 at 10:02

K T

169
1
4
14

I did consider this solution, but the problem is that the PK in the List table wouldn't be unique. – l0r3nz4cc10 Sep 24 '13 at 10:22

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

You need an "employee list" membership table. This is very similar to the concept of users and roles, where each user can be assigned to zero or more roles.

See How to design a user/role schema in a SQL Server database? for a good example datamodel; just rename "user" to "employee" and "role" to "list".

edited May 23 '17 at 11:50

Community

1
1

answered Sep 24 '13 at 10:20

Colin 't Hart

7,372
3
28
51

Database implementation

2 Answers2