Search code examples
sqlsql-servert-sqlrelational-databaserelational

SQL two tables and creating a link table

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

Solution

  • 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)
)