插入到sql数据库中的一对多关系

Guys I would really appreciate it if someone could help me with this. I have a problem inserting records into a one to many relationship database. The scenario is as follow, I have a technician table(techid,name,surname) and a skill table(skillid, skillDesc, techid). The skill table has a techID foreign key.

伙计们,如果有人能帮助我,我真的很感激。我在将记录插入一对多关系数据库时遇到问题。场景如下,我有技术员表(技术人员,姓名,姓氏)和技能表(技能,技能,技术)。技能表具有techID外键。

How can i create a sql statement/procedure to insert a technician with multiple skills assigned to the technician?

如何创建一个sql语句/过程来插入具有分配给技术人员的多种技能的技术人员?

2 个解决方案

#1

I think you should have three tables: Technician(techid, name, surname), Skill(skillid, skillDesc), and Ability(skillid, techid). If you only have two tables, each mapping of a skill to a technician would have to include a copy of the skillDesc, which is redundant information. Instead, you want to tables to define the Technician objects and the Skill objects, and a third table to capture the relationships between them.

我认为你应该有三个表:技术员(技术员,姓名,姓),技能(技能,技能)和技能(技能,技术)。如果您只有两个表,则技能与技术人员的每个映射都必须包含skillDesc的副本,这是冗余信息。相反,您希望表格定义技术人员对象和技能对象,以及第三个表格来捕获它们之间的关系。

First execute this query:

首先执行此查询:

insert into technician values (my_id, my_name, my_surname)

then loop over the skills (psuedocode):

然后循环技能(伪代码):

for skill in skills:
    insert into Ability values(skill, my_id)

#2

You need three tables Technician, Skill and TechnicianSkill. Using two tables you can not assign multiple skills to a single technician. ( IF TechID and SkillIDs are primary key )

你需要三张桌子Technician,Skill和TechnicianSkill。使用两个表格,您无法为单个技术人员分配多种技能。 (IF TechID和SkillID是主键)

Technician Table:
Columns: TechID, Name, SurName

Skill Table:
Columns: SkillID, SkillDesc

-- Put the TechID and SkillID as a foreign key in this table
TechnicianSkill Table:
Columns: TechID, SkillID 
-- You can make a primary key by using the two columns (TechID,SkillID)

#1