数据库计划 – 在数据库中存储总线路径
发布时间：2021-01-14 03:40:04 所属栏目：编程 来源：网络整理
导读：我做了一些研究,发明我应该将蹊径存储为一系列停靠点.就像是： 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")
（编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读