SQL两个表并创建一个链接表 [英] SQL two tables and creating a link table
问题描述
我有两个表:Employee (ID, Name, Address) 和 Store(ID,Address),我想记录每个商店工作人员的信息.
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.
我想创建一个名为 Employee_List 表的新表.我的问题:
I thought of making a new table called Employee_List table. My questions:
1- Employee_List 和 Employee 是一对多的关系吧?
1- Employee_List and Employee has one-to-many relation, right?
2- 要存储的 Employee_list 是一对一的关系,对吗?
2- Employee_list to store has one-to-one relation, right?
3- 如何为 Employee_List 表定义外键和主键?
3- How to define foreign and primary keys for Employee_List table?
推荐答案
Employee_list 应该有:
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.
在SQL Server
中:
CREATE TABLE EmployeeStores
(
EMPLOYEEStoreID INT IDENTITY,
EMPLOYEEID INT FOREIGN KEY REFERENCES Employee(employee_id),
STOREID INT FOREIGN KEY REFERENCES Store(store_id)
)
这篇关于SQL两个表并创建一个链接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!