涉及另一个表中多行的字段

I have created/am creating 3 tables: Slaves, Citizens and Incidents.

我创建/创建了3个表:奴隶,公民和事件。

How should I go about Incident involving multiple Citizens and Slaves? Now I'm thinking about making two fields in Incidents containing list of CitizenID's and SlaveID's (SlaveID1, SlaveID2...,SlaveIDn), but it seems plain dumb.

如何处理涉及多个公民和奴隶的事件?现在我正在考虑在事件中创建包含CitizenID和SlaveID(SlaveID1,SlaveID2 ......,SlaveIDn)列表的两个字段,但它看起来很愚蠢。

3 个解决方案

#1

Actually your idea doesn't sound dumb at all. You can design your Incidents table like this:

实际上你的想法听起来并不愚蠢。您可以像这样设计事件表:

+------------+-----------+---------+
| IncidentID | CitizenID | SlaveID |
+------------+-----------+---------+
|     1      |     A     |    A    |      <-- incident #1 involved 2 citizens and 1 slave
|     1      |     B     |    A    |
|     2      |     A     |    A    |      <-- incident #2 involved 2 citizens and 2 slaves
|     2      |     B     |    A    |
|     2      |     A     |    B    |
|     2      |     A     |    B    |
+------------+-----------+---------+

Now when you query for a certain incident ID you can obtain a list of all citizens and slaves involved in the incident. This is a many-to-many relationship in your database schema.

现在,当您查询某个事件ID时,您可以获得事件中涉及的所有公民和奴隶的列表。这是数据库模式中的多对多关系。

#2

You can make it by making 2 bridge table along with one master table

您可以通过制作2个桥接表和一个主表来实现

Master table
_______________
Incidents

Bridge tables
___________
incident_slave(pk of incidents table , slave information field(s) or pk of slave table)
incident_citizen(pk of incidents table , citizen information field(s) or pk of citizen table)

#3

You are looking for a many-to-many relationship.

您正在寻找多对多的关系。

Basicly you could get away with a table of kind:

基本上你可以摆脱一张桌子:

CREATE TABLE [dbo].[incidents](
    citizen_id*
    ,slave_id*
)

The * mark means column is a part of primary key. This ensures there is only one relationship between citizen John and slave Patrick.

*标记表示列是主键的一部分。这确保了公民约翰和奴隶帕特里克之间只有一种关系。

#1