CODEWITHSUNDEEP
mysqldatabase
ylin6
ylin6

Reputation: 63

Is this mySQL schema good practice?

I am fairly novice at mySQL and databases. I have a data set of bus-stops and bus routes. Bus stops can be contained in different bus routes. Is it good practice to have a Stop table with each stop's information, a routes table with each route information, then another link table to show which bus stops are in which bus routes? Here is the example schema below:

STOP

ROUTE

LINK?

Upvotes: 2

Views: 94

Answers (1)

Piet Lammers
Piet Lammers

Reputation: 31

I would say that it is important that you have an idea of the (mathematical) structure of the data. Often there are many ways to represent the same data and what is better depends on the purpose of your database. I will comment on your tables.

You are storing information on bus routes. You could see this as paths on a graph (mathematical represention). There are several ways to store this information. First of all, you will definitely need a table with the bus stops (vertices of the graph), so that cannot be wrong. Second, you need to store the paths. Here you have two options, you can either store them as a sequence of points, or a sequence of edges. The order is important. You do not currently store the order, I would definitely advise to put that in somewhere. You can also choose to store paths by edges, i.e., pairs of stops. Again, the order is important. You store for each route the start and end, you can choose to do this but it is important to understand that this is not per se necesarry (depending on the purpose of your db).

General advise: make clear how you want to represent your data on an abstract level.

Here my suggestion with minimal changes with respect to your table:

STOP

  • stop_id (PRIMARY KEY)
  • long
  • lat
  • avg_daily_boarders

ROUTE

  • route_id (PRIMARY KEY)
  • length
  • start
  • end

LINK?

  • entry_id (PRIMARY KEY)
  • stop_id (FOREIGN KEY) REFERENCES stop
  • route_id (FOREIGN KEY) REFERENCES route
  • sequence_nr such that stop_id is the sequence_nr-th stop on route route_id, so the pair (route_id,sequence_nr) must be unique

Upvotes: 3

Related Questions