帮助多对多关系

I have a problem with a many-to-many relation in my tables, which is between an employee and instructor who work in a training centre. I cannot find the link between them, and I don't know how to get it. The employee fields are:

我的表中存在多对多关系的问题,这是在培训中心工作的员工和讲师之间。我找不到他们之间的联系,我不知道如何得到它。员工字段是:

employee no.
employee name
company name
department job title

部门职称

business area
mobile number
ext
ranking

The Instructors fields are

教师领域是

instructor name
institute
mobile number
email address
fees

3 个解决方案

#1

in a many-to-many relationship the relationships will be in a 3rd table, something like

在多对多关系中,关系将在第3个表中,类似于

table EmployeeInstructor 
    EmployeeID
    InstructorID

to find all the employees for a specific instructor, you'd use a join against all three tables.

要查找特定教师的所有员工,您将对所有三个表使用联接。

#2

Or more likely there will be classes involved --

或者更有可能涉及课程 -

Employee takes Class Instructor teaches Class

员工需要班级讲师教授班级

so you'll have and EmployeeClass table,
an InstructorClass table,

所以你将拥有和EmployeeClass表,一个InstructorClass表,

and join through them. And Class needs to be unique, or else you'll need

并加入他们。而Class必须是独一无二的,否则你需要

Class is taught in Quarter on ClassSchedule

课程在Quarter on ClassSchedule上讲授

and end up joining EmplyeeClassSchedule to InstructorClassSchedule.

并最终将EmplyeeClassSchedule加入InstructorClassSchedule。

This ends up being one of your more interesting relational designs pretty quickly. If you google for "Terry Halpin" and "Object Role Modeling", this is used as an illustrative situation in the tutorial.

这很快成为你更有趣的关系设计之一。如果你谷歌搜索“Terry Halpin”和“对象角色建模”,这将在本教程中用作说明性情况。

#3

First of all, you will need a unique key in both tables. The employee number may work for the employee table, but you will need another for the instructor table. Personally, I tend to use auto incrementing identity fields called ID in my tables. This is the primary key. Second, create a new table, InstructorEmployee. This table has two columns, InstructorID and EmployeeID. Both fields should be indexed. Now you can create an association between any Employee and any Instructor by creating a record which contains the two IDs.

首先,您需要在两个表中使用唯一键。员工编号可以用于员工表,但是教师表需要另一个员工编号。就个人而言,我倾向于在我的表中使用名为ID的自动递增标识字段。这是主键。其次,创建一个新表,InstructorEmployee。该表有两列,InstructorID和EmployeeID。两个字段都应编入索引。现在,您可以通过创建包含两个ID的记录在任何Employee和任何Instructor之间创建关联。

#1