3

I'm Gergo Nagy, and I'm learning databases. My project is a bus schedule program, in android, but I stucked with the database part. My program will include 2 main parts: -If I click on a number of a bus, it shows the whole schedule, and a number, that shows when will the next bus start from the departure point. -if I click on the address of the bus stop, it shows which buses crosses this stop. But it only shows that buses, which arrives in an hour.

My problem is the many-to-many database, because one bus has a lot of stops, but a stop "has" a lot of buses. I'll attach a picture, from the schedule, I hope with this it will be easier to understand my question, 'cause my english is really bad, and sorry for that. Help for the question

I know it's a mess, but with this level of english knowledge, it's my best. I hope you can help me. Thank you in advance

  • 1
    Your engish isn't bad. Your problem is sufficiently described. No need to apologize. – danblack Sep 22 '18 at 23:40
2

Your bus schedule is actually much more complicated than just one many-to-many. There are actually at least a couple of many-to-many relationships needed to record the data in the schedule chart you've shown.

Consider this ERD:

ERD

Here is the type of data that would go in each table:

  • BUS_NUMBER has a bus #1. Maybe this also has a name or other information.
  • BUS_ROUTE_TYPE is where you have (1)a, (1)b, (1)c. It is a child table of BUS_NUMBER. Maybe different bus numbers don't have a, b, and c. Some may only have a, some may have more than three, etc.
  • STOP is the list of places where busses go, like Noszlopy G. park or Cifra Palota, etc. Note that the route type has an original starting location (origin) and a finishing location (destination). It also has a list of stops along the way, which are in TRAVEL_TIME_OFFSET (see next)
  • TRAVEL_TIME_OFFSET is a many-to-many intersection between a bus route type (e.g. 1b) and the places where the bus goes. For each stop along the way, the number of minutes of travel time to that stop from the origin is recorded. These are in your schedule chart as the numbers in circles on the left hand side. For example, for bus route 1b stop Piaristak tere is 0 minutes, Kodaly Iskola is 6 minutes, and Homokbanya kollegium is 15 minutes (including all the others along the way too)
  • SCHEDULE_TYPE is the list of different starting times - for example on school days, in summer workdays, free days, etc. These are the blocks of numbers on the right hand side of the chart (not the numbers in the blocks, the names of the blocks themselves)
  • DEPARTURE is also a many-to-many intersection. This is the list of times that a bus of a certain route type (e.g. 1c) leaves the origin stop, depending on what schedule type is in effect. For example, in Non-Working Days, Bus Route Type 1c departs at 20:40 and 22:30.

SAMPLE DATA

Here is some sample table to illustrate how to use these tables. I'm using meaningless (auto incremented) Surrogate Keys for the tables because that's probably what you want to use. I'm picking numbers for these to help make it clearer how records in one table join to others using primary and foreign keys.

BUS_NUMBER
==========
ID   CodeNumber Bus Route Name
---- ---------- --------------
33   1          Route 1
34   2          Route 2
...

BUS_ROUTE_TYPE
==============
ID   RouteTypeCode BusNumberID OriginStopID DestinationStopID Direction
---- ------------- ----------- ------------ ----------------- ---------
123  A             33          2345         2358              Out
124  B             33          2348         2358              Out
125  C             33          2345         2358              Out
126  A             33          2358         2345              Return
...

STOP
====
ID   Name
---- --------------------------------
2345 Noszlopy G. park
2346 Vasutallmmas
2347 Cifra Palota
2348 Piaristak tere
2349 Naiv Muveszeti Muzeum
2350 Katona J. Gimnazium
...
2357 Valyogveto utca
2358 Homokbanya kollegium

TRAVEL_TIME_OFFSET
==================
ID   BusRouteTypeID StopID Minutes
---- -------------- ------ -------
4567 123            2345   0
4568 123            2346   1
4569 123            2347   3
4570 123            2348   5
...
4579 123            2357   24
4580 123            2358   25

SCHEDULE_TYPE
=============
ID   Name 
---- ------------------------------
4    Iskolai eloadasi napokon
5    Tanszunetben munkanapokon
6    Szabadnapokon
7    Munkaszuneti napokon

DEPARTURE
=========
ID   ScheduleTypeID BusRouteTypeID StartTime
---- -------------- -------------- ---------
6123 4              125            04:35
6124 4              125            22:30
6125 4              124            04:40
6126 4              123            05:00
6126 4              123            05:15
6127 4              123            05:30
...
  • I've read this since 1 hour, but I there are a lot of questions in me. First of all, you said "here is the type of data that would go in each table". How many tables I actually need? For the first reading, i thought these 6 blocks are 6 different tables. But then I realized, it won't work, because there are a lot of buses, and lot of stops too. For example, there is bus #2, with a totally different route, bus number 3, and so on. With this model, I need at least 30 tables for the 30 different busses. Am I misunderstood your answer? – Gergő Nagy Sep 23 '18 at 7:42
  • 1
    @GergőNagy I think you are totally misunderstanding how relational databases work. There are six tables. The BUS_NUMBER table will have one record for each bus number (1, 2, 3,...). Similarly, every other table will have different records/rows for each bus schedule as necessary. You have one set of tables with multiple sets of data in each table. You should make sure that you understand how relational databases work and what foreign keys are and how to use them. – Joel Brown Sep 23 '18 at 15:09
  • I learnt relational databases in school, but I think I do not understand well. So, if I have 46 different buses, and about 200 different stops, how do I know which is the route? I started develop the database, but I'M stucked here: i.imgur.com/8vkROTP.png I have all buses, and all 3 different childs (a,b,c), and all stops (from all different 46 buses). But for exdample starting times should be an Array, in programming, and in one table there should be each array. I know it's basic, but after searching a day, I didn't find anything useful. – Gergő Nagy Sep 23 '18 at 16:40
  • (here is the whole pdf with all schedules: drive.google.com/file/d/12uE0mAUCwtlHelcq7hGMjA95y8UGCitS/… ) – Gergő Nagy Sep 23 '18 at 16:44
  • @GergőNagy See the sample data I added to my answer. If there are 46 buses there will be 46 records in the BUS_NUMBER table. Similarly 200 records in the STOP table. You know the route from the TIME_TRAVEL_OFFSET, which tells you for each route what stops are how many minutes away from the origin. You can sort by the number of minutes (ascending) to get a list of the stops in the correct sequence. Tables are more complex than arrays. You can think of a two dimensional array as like a table. Think more about a spreadsheet than an array and it might be clearer. – Joel Brown Sep 23 '18 at 17:42
3

You have these entities to convert in tables.

I suggest you build the fallowing tables in excel and see if all is there or something missing.

Stations : has name , and id .

Routes : connect stations and defines the bus time from one to another station.

Bus_and_start: when will every buss start and what route. Do you have a list of buses , like buss_long1 , bus_short1 , bus_extralarge4 ? Then split this table in 2 , the bus table and the start table. This would be good if a bus breaks or if you monitor real time if is to slow . Real_time monitoring will bring a list of challenges, not sure if this is part of your project.

To see for a station the next hour bus , you take all the routes that stop in that station and join with all bus_and_start and the route time to the bus start time and here is the list. If you need help , define the tables and I will help with the SQL statement.

  • note the Bus_and_start will have a field for 'school,summer,free,nonwork' – danblack Sep 22 '18 at 23:44
  • real_time monitoring isn't the part (now). I started wrote these to excel, but I think it doesn't look too good. I tried to store these in Access, but I haven't finished yet. (Because my the table defining problem) – Gergő Nagy Sep 23 '18 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.