SQL - a field that includes several rows from another table

Question

What I'm trying to find out has probably answered before, but I just can't find it here (since I'm not sure what I should look for), so naked with me.

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

How do I make an Incident involving several Citizens and Slaves ? Now I'm thinking of creating two fields in Incidents that list CitizenID and SlaveID (SlaveID1, SlaveID2 ..., SlaveIDn), but it seems like that is just plain stupid.

Answer 1

. Incidents :

+------------+-----------+---------+
| 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    |
+------------+-----------+---------+

, , , . " " .

Answer 2

, 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)

Answer 3

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

Basically you can get away with a table of the form:

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

The column labeled * is part of the primary key. This provides the only connection between citizen John and slave Patrick.