0

I have to design a database for buses timetables. Entities:

  • Bus (idBus*)
  • Stop (idStop*,stopDescription)
  • Line (idLine*,lineDescription)
  • Position (lat,lon)

Some constraints are the following:

  • Multiple Buses may operate for one Line (therefore BUS:LINE = N:1)
  • One Line has many Stops , and from one Stop are passing many Lines (therefore STOP:LINE = N:N)
  • One Bus passes from many Stops and vice versa (therefore BUS:STOP = N:N)
  • A Stop has One Position (therefore STOP:POSITION = N:N)
  • A Bus has multiple Positions (therefore BUS:POSITION = 1:N)



E-R DIAGRAM

enter image description here

An example of modelling would be a bridging table for the STOP-POSITION relationship that would look like this: STOP_POSITION(idStop*,lat,lon) whereas idStop is the Foreign Key.

In general:
If i have an idBus i would like to be able to get the associated idLine.
If i have an idBus and an idStop i would like to have info on the itinerary of the Bus (which is the next stop , time of arrival, direction)
If i have an idBus and an idLine i would like to get the itinerary of the Bus(all the Stops from where the Bus will pass and their order)


Questions

  1. The problem arise when considering the BUS-STOP relationship, because when i consider to know the id of the Stop and the id of the Bus then i will know a number of attributes like Direction,ID of NextStop, TimeOfArrival..
    How should i model those attributes?
    For example, every Bus is passing from multiple Stops and the progression is denoted by an attribute (e.g progressiveStop). How should i model this attribute?
  2. Does it really make sense modelling the association of LINE-STOP?
  3. Does it really make sense storing dynamic data in the database? I am referring to the BUS-STOP relationship.
yeaaaahhhh..hamf hamf
  • 746
  • 2
  • 13
  • 34
  • This isn't UML, is it? – qwerty_so Dec 01 '15 at 10:43
  • So why did you tag it UML? Oh, you removed the tag. Never mind :-) – qwerty_so Dec 01 '15 at 11:43
  • You have one important information missing - what is the required functionality? There is no exact anwer for your questions. 1. why do you have BUS - STOP relation? I think you don't need it.
    2. LINE - STOP is right, it models stops on one line
    3. what do zou mean with dynamic data?     – Petr Pribyl Dec 01 '15 at 12:15
  • @PetrPribyl i updated the post. Also with Dynamic Data i mean the position of the Bus in a certain time. This can be dynamic since i can pull it from the system in Real Time – yeaaaahhhh..hamf hamf Dec 01 '15 at 12:28
  • I'm still not sure, if do you need BUS - STOP relation. Sequence of stops is given by LINE - STOP relation, am I right? Ad Dynamic Data - I don't know :) It is given by your system requirements. for example: if you need to store history, store BUS - STOP relation. If not, don't do it :) – Petr Pribyl Dec 01 '15 at 13:00
  • I agree that a Bus-Stop relation is purely optional – yeaaaahhhh..hamf hamf Dec 02 '15 at 14:11

0 Answers0