Fiz algumas pesquisas e descobri que devo armazenar uma rota como uma sequência de paradas. Algo como:
Start -> Stop A -> Stop B -> Stop C -> End
Criei três tabelas:
- Rotas
- paradas
- Pontos de rota
...onde RouteStops é uma tabela de junção.
Eu tenho algo como:
Rotas
+---------+
| routeId |
+---------+
| 1 |
+---------+
| 2 |
+---------+
Estações
+-----------+------+
| 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 |
+-------------+---------------+
A rota 1 passa por
Station A -> Station C -> Station D
A rota 2 passa por
Station A -> Station D
Esta é uma boa maneira de armazenar rotas?
De acordo com a Wikipédia :
[...] o sistema de banco de dados não garante nenhuma ordem das linhas, a menos que uma
ORDER BYcláusula seja especificada [...]
Posso confiar em tal esquema de banco de dados ou talvez isso deva ser feito de maneira diferente?
Este é realmente o meu projeto universitário, então estou apenas imaginando se esse esquema pode ser considerado correto. Para este caso, eu provavelmente armazenaria apenas várias rotas (aproximadamente 3-5) e estações (aproximadamente 10-15), cada rota consistiria em cerca de 5 estações. Também ficaria feliz em saber como isso deve ser no caso de uma empresa de ônibus real e grande.
Para todas as análises de negócios que levam à arquitetura de banco de dados, recomendo escrever regras:
A 1ª e a 2ª regras, como você notou, implicam em um relacionamento muitos para muitos, então você concluiu corretamente para criar routeStations.
A terceira regra é a interessante. Isso implica que uma coluna extra é necessária para atender ao requisito. Para onde deve ir? Podemos ver que esta propriedade depende da Rota E da Estação. Portanto, ele deve estar localizado em routeStations.
Eu adicionaria uma coluna à tabela routeStations chamada "stationOrder".
Então a consulta se torna fácil:
Notas:
Para desenvolver no note 3, construí o caso de uso:
Este é o Oracle 12c Enterprise.
Observe que, no plano de execução abaixo, as rotas da tabela não são usadas. o Cost Base Optimizer (CBO) sabe que pode obter o routeId diretamente da chave primária de routeStations (etapa 5, INDEX RANGE SCAN em ROUTESTATIONS_PK, Predicate Information 5 - access("RS","ROUTEID"=1))
Agora a parte divertida, vamos adicionar um nome de coluna à tabela de rotas. Agora há uma coluna que realmente precisamos em "rotas". O CBO usa o índice para encontrar o rowID para a rota 1, então acessa a tabela (tabela de acesso pelo índice rowid) e captura a coluna "routes.name".
Você está certo, não há ordem inerente de registros em uma tabela relacional. Isso significa que você precisa fornecer alguma forma explícita de ordenar as estações dentro de cada rota.
Dependendo de como você está planejando acessar os dados, você pode
sequenceNumbercoluna toRouteStationspara armazenar, obviamente, a sequência de cada estação em cada rota.nextStationIdcoluna para armazenar um "ponteiro" para a próxima estação em cada rota.Não vi ninguém afirmar nada sobre isso, então pensei em adicionar para sua nota. Eu também colocaria um índice exclusivo não clusterizado (dependendo do seu RDBMS) na tabela RouteStations/RouteStops em todas as três colunas. Assim você não vai errar e o ônibus vai para as 2 próximas estações. Isso tornará mais difícil para atualizações, mas acho que ainda deve ser considerado como parte de um bom design.
Estou falando como um programador de aplicativos :
Nem pense em fazer roteamento ou tabela de horários com consultas no banco de dados (ou em um procedimento armazenado), nunca será rápido o suficiente. ( A menos que seja apenas um problema de “lição de casa”. )
Mesmo para um aplicativo que processa os dados na memória, o carregamento dos dados do banco de dados nunca será rápido, a menos que todos os dados sejam carregados na inicialização ou os dados sejam armazenados de forma desmoralizada. Uma vez que os dados são desmoralizados, há pouco sentido em usar um banco de dados relacional.
Portanto, pensaria no banco de dados como sendo a cópia “mestre” dos dados e aceitaria que também teria que armazená-lo pré-processado na memória do aplicativo ou em um servidor de caixa como o membase.
A resposta de ndefontenay fornece um bom design de tabela como ponto de partida, mas você deve considerar que as rotas têm horários diferentes dependendo da hora do dia e geralmente têm paradas diferentes dependendo da hora, dia da semana ou até férias escolares.