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)


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)


  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.

Your Answer

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

Browse other questions tagged or ask your own question.