数据库设计 – 在数据库中存储总线路径

我做了一些研究,发现我应该将路线存储为一系列停靠点.就像是：

Start -> Stop A -> Stop B -> Stop C -> End

我创建了三个表：

>路线
>停止
> RouteStops

…其中RouteStops是一个联结表.

我有类似的东西：

路线

+---------+
| routeId |
+---------+
|    1    |
+---------+
|    2    |
+---------+

站

+-----------+------+
| stationId | Name |
+-----------+------+
|     1     |   A  |
+-----------+------+
|     2     |   B  |
+-----------+------+
|     3     |   C  |
+-----------+------+
|     4     |   D  |
+-----------+------+

RouteStations

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     2       |       A       |
+-------------+---------------+
|     2       |       D       |
+-------------+---------------+

路线1通过

Station A -> Station C -> Station D

2号线经过

Station A -> Station D

这是存储路线的好方法吗？

根据Wikipedia：

[…] the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified […]

我可以依赖这样的数据库模式,或者这应该以不同的方式完成吗？

这实际上是我的大学项目,所以我只是想知道这样的模式是否可以被认为是正确的模式.对于这种情况,我可能只存储几条路线(大约3-5条)和站点(大约10-15条),每条路线将包含大约5个站点.我也很高兴听到真实和大型公交公司的情况如何.

解决方法

>一条路线有2个或更多的车站
>一个站可以被许多路线使用
>路线上的车站按特定顺序排列

您注意到的第一条和第二条规则意味着多对多的关系,因此您可以正确地创建routeStations.

第三条规则是有趣的.这意味着需要额外的列来满足要求.它应该去哪里？我们可以看到这个属性取决于Route AND St??ation.因此它应该位于routeStations中.

我会在表routeStations中添加一个名为“stationOrder”的列.

+-------------+---------------+---------------
| routeId(fk) | stationId(fk) | StationOrder |
+-------------+---------------+---------------
|     1       |       1       |       3      |
+-------------+---------------+---------------
|     1       |       3       |       1      |
+-------------+---------------+---------------
|     1       |       4       |       2      |
+-------------+---------------+---------------
|     2       |       1       |       1      |
+-------------+---------------+---------------
|     2       |       4       |       2      |
+-------------+---------------+---------------

然后查询变得容易：

select rs.routeID,s.Name
from routeStations rs
join
Stations s
on rs.stationId=s.StationId
where rs.routeId=1
order by rs.StationOrder;

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+

笔记：

>我在我的例子中修复了RouteStations中的StationId.您正在使用StationName作为Id.
>如果你不使用路由名称,那么甚至不需要routeId,因为你可以从routeStations获得它
>即使您要链接到路由表,您的数据库优化器也会注意到它不需要额外的链接,只需删除额外的步骤即可.

为了在注释3上开发,我构建了用例：

这是Oracle 12c Enterprise.

请注意,在下面的执行计划中,根本不使用表路由. Cost Base Optimizer(CBO)知道它可以直接从routeStations的主键获取routeId(步骤5,ROUTESTATIONS_PK上的INDEX RANGE SCAN,谓词信息5 – 访问(“RS”.“ROUTEID”= 1))

--Table ROUTES
create sequence routeId_Seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

CREATE TABLE routes
(
  routeId  INTEGER NOT NULL
);


ALTER TABLE routes ADD (
  CONSTRAINT routes_PK
  PRIMARY KEY
  (routeId)
  ENABLE VALIDATE);

insert into routes values (routeId_Seq.nextval);
insert into routes values (routeId_Seq.nextval);
commit;

--TABLE STATIONS  
create sequence stationId_seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

create table stations(
   stationID INTEGER NOT NULL,name varchar(50) NOT NULL
);

ALTER TABLE stations ADD (
  CONSTRAINT stations_PK
  PRIMARY KEY
  (stationId)
  ENABLE VALIDATE);

insert into stations values (stationId_seq.nextval,'A');
insert into stations values (stationId_seq.nextval,'B');
insert into stations values (stationId_seq.nextval,'C');
insert into stations values (stationId_seq.nextval,'D');
commit;
--

--Table ROUTESTATIONS 
CREATE TABLE routeStations
(
  routeId       INTEGER NOT NULL,stationId     INTEGER NOT NULL,stationOrder  INTEGER NOT NULL
);


ALTER TABLE routeStations ADD (
  CONSTRAINT routeStations_PK
  PRIMARY KEY
  (routeId,stationId)
  ENABLE VALIDATE);

ALTER TABLE routeStations ADD (
  FOREIGN KEY (routeId) 
  REFERENCES ROUTES (ROUTEID)
  ENABLE VALIDATE,FOREIGN KEY (stationId) 
  REFERENCES STATIONS (stationId)
  ENABLE VALIDATE);

insert into routeStations values (1,1,3);
insert into routeStations values (1,3,1);
insert into routeStations values (1,4,2);
insert into routeStations values (2,1);
insert into routeStations values (2,2);
commit;

explain plan for select rs.routeID,s.Name
from ndefontenay.routeStations rs
join
ndefontenay.routes r
on r.routeId=rs.routeId
join ndefontenay.stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 1000
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2617709240                                                                                                                                                                                                                                                                                 

---------------------------------------------------------------------------------------------------                                                                                                                                                                                                         
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------                                                                                                                                                                                                         
|   0 | SELECT STATEMENT               |                  |     1 |    79 |     1 (100)| 00:00:01 |                                                                                                                                                                                                         
|   1 |  SORT ORDER BY                 |                  |     1 |    79 |     1 (100)| 00:00:01 |                                                                                                                                                                                                         
|   2 |   NESTED LOOPS                 |                  |       |       |            |          |                                                                                                                                                                                                         
|   3 |    NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |                                                                                                                                                                                                         
|   4 |     TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |                                                                                                                                                                                                         
|*  5 |      INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                         
|*  6 |     INDEX UNIQUE SCAN          | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                         
|   7 |    TABLE ACCESS BY INDEX ROWID | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |                                                                                                                                                                                                         
---------------------------------------------------------------------------------------------------                                                                                                                                                                                                         

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         

   5 - access("RS"."ROUTEID"=1)                                                                                                                                                                                                                                                                             
   6 - access("RS"."STATIONID"="S"."STATIONID")

（编辑：清远站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!