11

I have two tables: Employee (ID, Name, Address) and Store(ID,Address) and I would like to record information about people who work in each store.

I thought of making a new table called Employee_List table. My questions:

1- Employee_List and Employee has one-to-many relation, right?

2- Employee_list to store has one-to-one relation, right?

3- How to define foreign and primary keys for Employee_List table?

enter image description here

Darren
  • 68,902
  • 24
  • 138
  • 144
hamid
  • 2,033
  • 4
  • 22
  • 42
  • Why don't you just add an attribute worksIn to employee? The attribute values of worksIn are store_id values then. worksIn is a foreign key on store(store_id). – muehlbau Feb 20 '13 at 11:06
  • Unless an employee works in more than one store, what @muehlbau said, just add column store_id to employee – Popnoodles Feb 20 '13 at 11:07
  • True @popnoodles. If an employee can work in more than one store than create a new relation works_in(employee_id,store_id) with primary key(employee_id,store_id) and foreign key on employee(employee_id) and store(store_id). – muehlbau Feb 20 '13 at 11:09
  • some employees (i.e. managers) work in more than one store. – hamid Feb 20 '13 at 11:10
  • @Sam - the solution I provided allows for multiple employees in more than one store. I have gave my answer using using SQL Server, is that the RDMS you are using? – Darren Feb 20 '13 at 11:15
  • @DarrenDavies Yes it is – hamid Feb 20 '13 at 11:17
  • @Sam - check out my answer it should help. – Darren Feb 20 '13 at 11:19
  • @DarrenDavies just one question, since employee_list to employee has one-to-many relation, is it necessary to add a foreign key to employee for employee_list? – hamid Feb 20 '13 at 11:28
  • @Sam - yes, the foreign key will ensure referential integrity. For example, the value Employee_Id in Employee_List is a valid Employee. – Darren Feb 20 '13 at 11:32

1 Answers1

20

Employee_list should have:

  • employee_listid (INT PK)
  • employee_id (INT FK)
  • store_id (INT FK)

I would recommend changing the table name to represent the composite table, i.e. EmployeeStores. This would allow your schema to be scalable, employees can work in multiple stores.

In SQL Server:

CREATE TABLE EmployeeStores
(
   EMPLOYEEStoreID   INT IDENTITY,
   EMPLOYEEID INT FOREIGN KEY REFERENCES Employee(employee_id),
   STOREID INT FOREIGN KEY REFERENCES Store(store_id)
)
Darren
  • 68,902
  • 24
  • 138
  • 144
  • 9
    You don't need an employee_listid. PK is employee_id,storeid. – muehlbau Feb 20 '13 at 11:10
  • @muehlbau PK of the new table would not be employee_id – Popnoodles Feb 20 '13 at 11:12
  • @muehlbau - Employee_id and storeID are composite keys. You can still have EmployeeStoreID or EmployeeListID which would make it easier to find records in the new table. - This should not warrant a downvote as it is perfectly valid. – Darren Feb 20 '13 at 11:12
  • @popnoodles maybe my comment is not clear but it's eployee_id and store_id together. – muehlbau Feb 20 '13 at 11:13
  • @muehlbau - yes a composite key. However you can still have EMPLOYEESTOREID to reference the table as the main identifier, just like EMPLOYEEID. – Darren Feb 20 '13 at 11:14
  • 1
    @DarrenDavies I don't say you can't have it but why introduce a new artificial key, waste space, and not just use the composite key? It does not make it easier to find records btw; working on core databases I can assure you that query optimizers often even profit from composite keys. – muehlbau Feb 20 '13 at 11:15
  • @muehlbau - depends entirely on how the internals handle it, you cannot guarentee that the execution plan will choose a composite key each time. Plus, it's not a waste of space, what if I wanted to find the first ever entry in the database, how would I assume that with no datetime value? I could at least make an assumption based upon the new field introduced since it is an identity column. – Darren Feb 20 '13 at 11:17
  • 1
    @DarrenDavies yes, I cannot guarantee you that every query engine will profit from it - however you cannot say there is no waste of space (each entry in the list has an overhead of at least 4 Bytes for the artificial key). Your finding the first created value use case is an even worse use case: who says that the first entry is not deleted and a new entry is inserted with artificial key 1? My arguments come from a relational design point of view - sry for the downvote btw, that was a bit harsh, but I'm emotional when it comes to wrong assumptions in relational design. – muehlbau Feb 20 '13 at 11:21
  • @muehlbau - it is an identity column, SQL Server holds the seed so the column value will never be 1 again unless the DBA resets the identity manually for that table. I understand and respect your point of view, both answers are correct, I just prefer consistency (Since the OP is using artificial keys for Employee and Store tables) and IMO it is easier to find records using surrogate keys. – Darren Feb 20 '13 at 11:24