SQL two tables and creating a link table

sql sql-server tsql relational-database relational
Hamid picture Hamid · Feb 20, 2013 · Viewed 37.1k times · Source

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

Answer

Darren picture Darren · Feb 20, 2013

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More