Rozkład jazdy busów - baza danych

Question

Cześć, mam problem z bazą. Jakiś czas temu utworzyłem sobie bazę która ma trzymać dane dotyczące rozkładu jazdy busów (przystanki, odjazdy, trasy, miasta, przewoźnicy). Uzupełniłem ją sobie przykładowymi danymi i zrobiłem proste kwerendy gdzie wystarczyło join'ować i poszło bez problemu (np. wybór wszystkich linii, przystanków czy wszystkich linii przejeżdżających przez dany przystanek). Od 2 dni nie mogę natomiast ogarnąć najważniejszej kwerendy, otóż potrzebuję mając id 2 przystanków (nazwijmy je A i B) znaleźć wszystkie linie które przejeżdżają przez A i B i obliczyć czas odjazdu z przystanku A, czas dojazdu do przystanku B. W bazie są wszystkie potrzebne informacje tylko trzeba się odpowiednio do nich dostać. 

Prosiłbym o pomoc w chociaż naprowadzeniu jak to ogarnąć.

Poniżej zamieszczam model bazy - wydaje mi się że jest dobrze zrobiona (przerabiałem strukturę kilka razy i posiłkowałem się informacjami z różnych for i stron).

 

Co do tabel to tak:

przewoźnicy, przystanki i miasta nie wymagają wyjaśnienia,

w liniach jest tylko informacja jaki przewoźnik jeździ na jakiej linii (może być kilka linii z tego samego punktu początkowego i tego samego końcowego bo różni przewoźnicy mogą jeździć różnymi trasami), 

w tabeli odjazdy jest informacja o której godzinie rozpoczyna kurs bus określonej linii i w jakie dni jeździ,

tabela trasy ma w sobie informacje o tym jakie przystanki są na danej linii, w jakiej kolejności oraz jaki jest czas przejazdu z przystanku wcześniejszego (0 jeśli przystanek jest pierwszy w kolejności).

 

A tu jest skrypt do odtworzenia mojej bazy: https://www.dropbox.com/s/g21t1lvwt4gxc6q/sql8163456.sql?dl=0 .

Bardzo bym prosił o wskazanie kierunku w jakim powinienem pójść żeby to rozwiązać czy też sugestię dotyczące budowy bazy.

Comments

Tutaj zamieszczam kilka select'ów które udało mi się skonstruować (w bazie tabele i kolumny mam w języku angielskim a na modelu w języku polskim, ale tłumaczone 1:1 więc nie powinno być problemu).

//zwraca wszystkie linie które przejeżdżają przez punkt A i B (tu jest taki problem że wyciaga nawet te które jadą B-A, i to powinno być sprawdzane na podstawie kolejności przystanków ale nie udało mi się tego zrobić jeszcze)

select DISTINCT id_line from bus_routes where bus_routes.id_line in (select id_line from bus_routes where bus_routes.id_stop = A) and bus_routes.id_line in (select id_line from bus_routes where bus_routes.id_stop = B);

 

//zwraca ilość sekund od początku kursu do dojazdu do przystanku A na linii L

select id_line, sum(time_to_sec(time_from_previous)) as dep from bus_routes where id_line = L and order_number <= (select order_number from bus_routes where id_line = L and id_stop = A);

 

//zwraca wszystkie odjazdy dla linii które przejeżdzają przez A i B (linie zwracane z pierwszego zapytania)

select * from departures where id_line in (select DISTINCT id_line from bus_routes where bus_routes.id_line in (select id_line from bus_routes where bus_routes.id_stop = A) and bus_routes.id_line in (select id_line from bus_routes where bus_routes.id_stop = B));

---

Nie masz informacji o przyjazdach. Musisz kombinować. Z skąd wiesz o której, który bus przyjedzie na przystanek B?

Musisz mieć jeszcze dwie tabele: przyjazdy oraz tebele łączącą odjazdy z przyjazdami, np połączenie.

---

wydaje mi się że czas kiedy bus przyjeżdża na przystanek B mogę obliczyć tak jak w 2 kwerendzie w komentarzu: mając id przystanku (B) i id linii (L), wiem o której godzinie busy rozpoczynają kursy (z tabeli odjazdy). Dodaję do tej godziny odjazdu zsumowany czas_z_poprzedniego (z tabeli trasy) gdzie kolejność jest mniejsza lub równa kolejności przystanku B na linii.

Czasy przyjazdów na konkretne przystanki są w tabeli trasy (możliwe że niezbyt trafie nazwałem tabelę) w kolumnie czas_z_poprzedniego.

Mógłbyś ewentualnie coś więcej powiedzieć o tych proponowanych kolejnych 2 tabelach?

Czasy przyjazdów można obliczyć na podstawie czasu odjazdu (tabela odjazdy) oraz czasów między przystankami (tabela odjazdy). Przykładowa trasa w tablicy trasy wyglada tak:

 

---

Coś takiego:

---

Dodałem wspomniane przez Ciebie tabele i wyszło mi coś takiego.

Przy czym mam wrażenie że dane się w tym przypadku będą dublowały. Nie wiem czy w pełni rozumiem jaką rolę te tabele mają odgrywać. W tabeli 'connections' planowałeś dodać tylko przystanek początkowy i końcowy linii ? Jeśli tak, to te same dane znajdują się w tabeli 'routes' (początkowy w rekordzie gdzie order_number = 0, a końcowy w najwyższym order_number dla danej linii).

 

Wszystkie przystanki dla danych linii planowałem wrzucić do 'routes' i stamtąd wyciągać właśnie na podstawie konkretnej linii. Wtedy z tym chyba by nie byłoby problemu jeśli przez 1 przystanek przejeżdżała by tylko 1 linia, a  przejeżdża kilka.

 

Trochę to zagmatwane ;) Na początku to nie sprawiało wrażenia tak skomplikowanego (no chyba że to ja tu strasznie mieszam). 

---

Zawsze tak jest że na początku wszystko jest jasne a z czasem dochodzi problemów.

Wracając do Twojej bazy. To z pól w tabeli `routes` jasno nie wynika co chciałeś nią osiągnąć.

Ogólnie chodziło mi że potrzebujesz tabeli do trzymania takich informacji: nr_lini, przystanek_start, przystanek_stop, czas_przejazdu.

Jeżeli uważasz że `routes` Ci wystarczy to ok.

---

Do tabeli 'lines' dodam id_przystanku_start i id_przystanku koniec żeby nie trzeba było z 'routes' joinować i szukać kolejności aby wyciągnąć początek i koniec linii.

Chcę jak najmniej używać 'sztywnych' godzin aby rozkład był jak najbardziej elastyczny (ciężko wpisać dla kilku przejazdów na sztywno godziny odjazdów z każdego pojedynczego przystanku) i ograniczam to tylko do czasu początku kursu - czas odjazdu/dojazdu z/do konkretnego przystanku powinnien być obliczalny właśnie na podstawie czasu początku kursu i dodaniu do niego sumy 'czas_z_poprzedniego' z 'routes' przystanków których kolejność jest mniejsza od kolejności szukanego przystanku.

Chcę jak najmniej bawić się wynikami w PHP i wykonywaniu wielu zapytań ale chyba tego nie uniknę ;) Dzięki za pomoc. Będę dalej kobinował ;)

---

Operacje matematyczne można wykonywać po stronie bazy danych - link.