这两个表需要什么样的关系? [英] What kind of relationship do these 2 tables require?

问题描述

我有2张桌子，想知道它们之间的最佳关系是什么.我知道它们之间存在关系，但是我对单向，双向，多向等一对多，多对一，多对多感到困惑.

I have 2 tables and was wondering what the best relationship between them was. I know there is a relationship between them but I get so confused with one to many, many to one, many to many, unidirectional, bidirectional, multidirectional etc.

这是显示的基本结构:

旅行者表:

+------------------------------------------+
| Name     | Family Name | National ID No. |
+------------------------------------------+
| Dianne   | Herbert     | 579643          |
| Francine | Jackson     | 183432          |
| Oprah    | Dingle      | 269537          |
+------------------------------------------+

旅程表

+------------------------------------------------------------------------------------------------------+
| Start Station | End Station | Start Time | End Time | Travelers                                      |
+------------------------------------------------------------------------------------------------------+
| Hull          | Leeds       | 13:50      | 14:50    | Francine Jackson, Oprah Dingle                 |
| Newcastle     | Manchester  | 16:30      | 19:00    | Dianne Herbert, Francine Jackson               |
| Hull          | Manchester  | 10:00      | 13:00    | Dianne Herbert, Francine Jackson, Oprah Dingle |
+------------------------------------------------------------------------------------------------------+

旅行者表还可以，这很有意义:

The travelers table is okay, it makes sense:

CREATE TABLE Travelers ( 
    Name VARCHAR(50) NOT NULL,
    Family_Name VARCHAR(50) NOT NULL,
    National_ID_Number INT(6) NOT NULL PRIMARY KEY
)

但是我不确定如何进行旅程表.尤其是对于旅行者:

But I am unsure about how to do the journeys table. Especially with Travelers:

CREATE TABLE Journeys (
    Start_Station VARCHAR(50) NOT NULL,
    End_Station VARCHAR(50) NOT NULL,
    Start_Time VARCHAR(50) NOT NULL,
    End_Time VARCHAR(50) NOT NULL,
    Travelers ???????
)

很明显，我在第二张表中有旅行者"列.因此，与第一个表存在关系.那是什么我想我需要以某种方式制作外键?

Obviously I have "Travelers" as a column inside my 2nd table. So there is a relationship there with the first table. But what is it? I think I need to make a Foreign Key somehow?

推荐答案

您正在寻找联结/关联表.这些表应如下所示:

You are looking for a junction/association table. The tables should look like this:

create table Journeys (
    Journey_Id int auto_increment primary key,
    Start_Station VARCHAR(50) NOT NULL,
    End_Station VARCHAR(50) NOT NULL,
    Start_Time VARCHAR(50) NOT NULL,
    End_Time VARCHAR(50) NOT NULL
)

create table TravelerJourneys (
    traveler_journey_id int auto_increment primary key,
    traveler_id int(6),
    journey_id int,
    foreign key (traveler_id) references travelers(National_ID_Number),
    foreign key (journey_id) references Journeys (journey_id)
);

这篇关于这两个表需要什么样的关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！