数据库设计 – 在数据库中存储总线路径
发布时间：2021-01-14 00:35:09 所属栏目：MsSql教程 来源：网络整理
导读：我做了一些研究,发现我应该将路线存储为一系列停靠点.就像是： Start - Stop A - Stop B - Stop C - End 我创建了三个表： 路线 停止 RouteStops …其中RouteStops是一个联结表. 我有类似的东西： 路线 +---------+| routeId |+---------+| 1 |+---------+| 2
|
副标题[/!--empirenews.page--]
我做了一些研究,发现我应该将路线存储为一系列停靠点.就像是： Start -> Stop A -> Stop B -> Stop C -> End 我创建了三个表： >路线 …其中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：
我可以依赖这样的数据库模式,或者这应该以不同的方式完成吗？ 这实际上是我的大学项目,所以我只是想知道这样的模式是否可以被认为是正确的模式.对于这种情况,我可能只存储几条路线(大约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. 为了在注释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")
（编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
