在数据库中存储两个位置之间的路线

Question

有人可以告诉我，如何设计一个数据库，可以存储

• 起始位置（城市名称）
• 之间的位置（城市名称黑白起始位置和结束位置） 位置，数据库应该能够处理任意数量的位置）
• 结束位置（城市名称）

谢谢！

编辑：

例如，我必须保存芝加哥和纽约之间的地点。 我不确定如何设计一个可以容纳

• 起始地点（芝加哥）
• 之间位置的数据库（我主要对这里的数据类型感到困惑）
• 结束地点（纽约）

Answer 1

我有三个表：位置、路线和停靠点

locations:
    location_id | name

routes:
    route_id | description

stops:
    stop_id | location_id (fk) | route_id (fk) | stop_number

停靠点表是交叉链接表。在插入位置时，让应用程序设置正确的停止编号。
然后，您可以使用简单的方法获取路线。

select * from routes join stops join location
where route_id = ...    
order by stop_number

如果您需要将位置插入路线，请使用两个查询：

update stops set stop_number = stop_number + 1 
where route_id = ... 
   and stop_number > 'inserted_stop_number';

insert into stops (route_id, location_id, stop_number) values // etc

理论上，链接列表（在其中存储对路线上下一站的引用）在概念上更接近现实，但在标准的 Mysql-options 中，从以这种方式存储的数据中获取列表非常困难，所以我建议不要这样做，并让自己保持简单。

如果您确实需要在路线中创建和使用链接节点的更多功能，您可以考虑学习： http://openquery .com/graph/doc，但对于不需要一直重新计算的简单的从开始到结束的路线，上面的内容很可能就足够了。

Answer 2

您应该有另一个可以引用的表，该表与所有开始、中转和结束记录具有一对多关系，因此您可以将整个记录“集合”“绑定”在一起。

行程

trip_id | ...

地点

location_id | trip_id | location_type | location

在 location_type 列中，您可以有一些独特的标志，这些标志将告诉您位置记录是否表示出发、中转或 >完成。例如，开始可以是 0 标志，1 可以是中转标志，2 可以是结束标志。

Answer 3

未经测试

CREATE TABLE Location (
    ID INT UNSIGNED AUTO_INCREMENT,
    Name VARCHAR(50),
    PRIMARY KEY (ID)
) ENGINE=INNODB CHARACTER SET utf8

CREATE TABLE Trip (
    ID INT UNSIGNED AUTO_INCREMENT,
    Name VARCHAR(50),
    StartLocation INT UNSIGNED,
    EndLocation INT UNSIGNED,
    PRIMARY KEY (ID),
    CONSTRAINT Constr_Trip_StartLocation_fk
        FOREIGN KEY Trip_StartLocation_fk (StartLocation)
        REFERENCES Location (ID),
    CONSTRAINT Constr_Trip_EndLocation_fk
        FOREIGN KEY Trip_EndLocation_fk (EndLocation)
        REFERENCES Location (ID)
) ENGINE=INNODB CHARACTER SET utf8

CREATE TABLE TripStopoff (
    Trip INT UNSIGNED,
    StopoffNumber INT UNSIGNED,
    Location INT UNSIGNED,
    PRIMARY KEY (Trip, StopoffNumber),
    CONSTRAINT Constr_TripStopoff_Trip_fk
        FOREIGN KEY TripStopoff_Trip_fk (Trip)
        REFERENCES Trip (ID),
    CONSTRAINT Constr_TripStopoff_Location_fk
        FOREIGN KEY TripStopoff_Location_fk (Location)
        REFERENCES Location (ID)
) ENGINE=INNODB CHARACTER SET ascii

INSERT INTO
    Location
    (ID, Name)
VALUES
    (1, 'Chicago'),
    (2, 'Manoning'),
    (3, 'Jersey City'),
    (4, 'Newark'),
    (5, 'New York')

INSERT INTO
    Trip
    (ID, Name, StartLocation, EndLocation)
VALUES
    (1, 'My trip', 1, 5)

INSERT INTO
    TripStopoff
    (Trip, StopoffNumber, Location)
VALUES
    (1, 1, 2),
    (1, 2, 3),
    (1, 3, 4)