关联两个数据库表（将员工与活动相关联） [英] Relating two database tables (associating an employee with an activity)

问题描述

我正在尝试开发数据库来存储跟踪员工活动的基于Web的应用程序收集的数据。我有一张员工表，看起来像这样

I am trying to develop a database to store data collected by a web based application that tracks employee activities. I have an employee table that looks like this

Employee
--------
id
name
position
email

和多个活动表，每个都有不同的列。例如：

And and multiple activity tables, each with different columns. An example of one:

OutreachAndTraining
-----
id
date
county
city
type
...

ProfessionalDevelopment
------
id
date
comments

我想能够跟踪哪些员工是每个员工的一部分活动，但只想记录活动本身一次。将多个员工与一个活动相关联的最佳方式是什么？我必须为每个活动创建一个单独的表来存储员工ID和活动ID？

I want to be able to keep track of which employees were a part of each activity but only want to log the activity itself once. What is the best way to associate multiple employees with one activity? Will I have to create a separate table for each activity to store employee IDs and activity IDs?

推荐答案

假设员工可以参加在一个以上的活动中，您所拥有的是员工与活动之间的N：M关系，每个关系中都附加了链接表：

Assuming an employee can take part in more than one activity, what you have here is an N:M relationship between employees and activities, which is modeled with the additional "link" table per each relationship:

如果您有多种活动，可以考虑继承（也称为类别，泛化，子类型或类层次），以最小化关系数（因此链接表）：

If you have many kinds of activities, you may consider inheritance (aka. category, generalization, subtype or class hierarchy), to minimize the number of relationships (and therefore "link" tables):

有关继承的更多信息，请在 ERwin方法指南。

For more information on inheritance, search for "Subtype Relationships" in ERwin Methods Guide.

这篇关于关联两个数据库表（将员工与活动相关联）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！