我做了一些研究,发现我应该将路线存储为一系列停靠点.就像是：
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 BYclause is specified […]
我可以依赖这样的数据库模式,或者这应该以不同的方式完成吗？
这实际上是我的大学项目,所以我只是想知道这样的模式是否可以被认为是正确的模式.对于这种情况,我可能只存储几条路线(大约3-5条)和站点(大约10-15条),每条路线将包含大约5个站点.我也很高兴听到真实和大型公交公司的情况如何.
解决方法
对于导致数据库体系结构的所有业务分析,我建议编写规则：
>一条路线有2个或更多的车站
>一个站可以被许多路线使用
>路线上的车站按特定顺序排列
您注意到的第一条和第二条规则意味着多对多的关系,因此您可以正确地创建routeStations.
第三条规则是有趣的.这意味着需要额外的列来满足要求.它应该去哪里？我们可以看到这个属性取决于Route AND Station.因此它应该位于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")
现在是有趣的部分,让我们在路由表中添加一个列名.
现在我们在“路线”中确实需要一个专栏.
CBO使用索引查找路由1的rowID,然后访问该表(通过索引rowid访问表)并获取列“routes.name”.
ALTER TABLE ROUTES ADD (name VARCHAR2(50)); update routes set name='Old Town' where routeId=1; update routes set name='North County' where routeId=2; commit; explain plan for select r.name as routeName,s.Name as stationName from routeStations rs join routes r on r.routeId=rs.routeId join stations s on rs.stationId=s.stationId where rs.routeId=1 order by rs.StationOrder; set linesize 500 set pages 500 select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- Plan hash value: 3368128430 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 119 | 1 (100)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 119 | 1 (100)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 1 | 119 | 0 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 79 | 0 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| ROUTES | 1 | 40 | 0 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | ROUTES_PK | 1 | | 0 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS | 1 | 39 | 0 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN | ROUTESTATIONS_PK | 1 | | 0 (0)| 00:00:01 | |* 9 | INDEX UNIQUE SCAN | STATIONS_PK | 1 | | 0 (0)| 00:00:01 | | 10 | TABLE ACCESS BY INDEX ROWID | STATIONS | 1 | 40 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("R"."ROUTEID"=1) 8 - access("RS"."ROUTEID"=1) 9 - access("RS"."STATIONID"="S"."STATIONID")
