Modelling a database for a travelling business that involves Vehicules, Routes, Subroutes and Prices

I did similar work to this many moons ago - I worked for an airline, but it's more or less the same thing!

A critical thing to get right from the start is terminology - yes, I know that you just want to start coding! :-) However, life will be much easier if everyone is singing off the same hymnsheet!

In the airline for which I worked, the word "flight" had three different meanings depending on whether you were speaking to an engineer, flight crew or sales people!

I would use the word (and table name) "leg" for a journey from one point to another where passengers can get on or off.

A "trip" can be many legs, normally, but not necessarily, a journey from point A to point B and back to point A "series" can be a number of trips over a season (don't know if this is your scenario, but it might help to have an over-arching framework and then factor in exceptions? You might have prices for legs and another price for a trip (normally lower than the sum of the legs)?

Firstly, on a technical note, I would advise you to use PostgreSQL (way before MySQL) for 5 main reasons (see below). If you want an embedded database, use Firebird which again has far more features (and has had them far longer) than MySQL.

1) it is rock-solid,

2) also has CTEs (i.e. WITH clause - also in MySQL 8 - but that is alpha)

3) Analytic functions (again, in MySQL 8?)

4) JSON functions

5) a rich language (PL/pgsql) that is comparable to Oracle's.

Note that (for PostgreSQL at any rate), the DDL (Data Definition Language) - the code that creates tables and indexes &c... is a powerful programming tool in its own right - it can save you lots of coding and error-checking in your application code. In fairness, all of the mainstream databases have similar functionality. PostgreSQL however is excellent (the best IMHO) for standards compliance!

My philosophy is always to put as much logic in the database as possible - development environments come and go, databases and data (like diamonds :-) ) are forever!

p.s. you may wonder why my names for fields, constraints, indexes &c. seem long and maybe to some, overly verbose. This is because I have learnt through painful experience that calling the auto-incrementing PRIMARY KEY field for every table 'id' is a recipe for disaster - as is letting the RDBMS assign its own machine-generated names to database objects.

I would start by creating the following tables:

CREATE TABLE location
(
  location_id SERIAL CONSTRAINT location_pk PRIMARY KEY,

  -- I find that keeping CONSTRAINT declarations in the CREATE TABLE 
  -- statement makes them easier to follow and is more elegant.
  -- Furthermore, I find that keeping the declarations of CONSTRAINTs
  -- as near to the relevant FIELD as possible also helpful.

  -- I *_always_* give meaningful names to my CONSTRAINTs - makes
  -- debugging *_much_* easier - here location_pk makes more sense than
  -- some machine-generated gibberish. Oracle generates particularly 
  -- opaque names for keys and other constraints. Use the capabilities
  -- of the database to the maximum!

  -- PRIMARY KEY name is **_always_** (specified by the programmer as) table_name_pk, guaranteed to be UNIQUE in 
  -- the entire system. I always have singular table names - they are the equivalent
  -- of class definitions in OO programming. **_Plus_**, you may well want tables
  -- with 1 or even 0 records (think system = Nuclear_Power_Plant - table = 
  -- Catastrophic_meltdown! :-)

  location_name VARCHAR(30) NOT NULL CONSTRAINT location_name_uq UNIQUE

  -- You might want to add GPS coordinates?

);

Then, insert some (sample/real) data - note that PostgreSQL takes care of the SERIAL column incrementing!

INSERT INTO location (location_name) VALUES ('One');
INSERT INTO location (location_name) VALUES ('Two');
INSERT INTO location (location_name) VALUES ('Three');
INSERT INTO location (location_name) VALUES ('Four');

Now, create another table leg (more complex this time). The leg table is just a list of departure points, arrival points, departure points and amounts. You will have to determine if your system has to allow for simple "there and back" runs (over several locations) or more complex routes (i.e. trips).

CREATE TABLE leg
(
  leg_id SERIAL CONSTRAINT leg_pk PRIMARY KEY,

  leg_location_dep INTEGER,  -- is an FK field - no need to specify NON NULL!
  leg_location_arr INTEGER,
  leg_amount INTEGER NOT NULL CHECK (leg_amount > 0),

  -- ADD FKs to location table.

  CONSTRAINT leg_location_dep_FK FOREIGN KEY (leg_location_dep) REFERENCES location (location_id),
  CONSTRAINT leg_location_arr_FK FOREIGN KEY (leg_location_arr) REFERENCES location (location_id),

  -- Your app will *_never_* have to check that a fare is not quoted from one place back 
  -- to the same place. If you are going to do return fares, then I would combine trips
  -- in some way.

  CONSTRAINT leg_dep_ne_arr_ck CHECK (leg_location_dep <> leg_location_arr),

  -- This last constraint ensures that you can never have two
  -- different fares for the same leg - this *_may_* or *_may_*
  -- suit your requirements - what you can see though, is that 
  -- with intelligent use of the `CREATE TABLE` statement, you
  -- can already put a *_lot_* of logic into your database without
  -- writing a single bit of client code! You may also want to
  -- different fares for different times of day, week, year.

  CONSTRAINT leg_dep_arr_uq UNIQUE (leg_location_dep, leg_location_arr)

  );


CREATE INDEX leg_dep_idx ON fare (leg_location_dep); 
CREATE INDEX leg_arr_idx ON fare (leg_location_arr); 

The construction of INDEXes is a science in its own right and any indexing strategy will depend on the queries that you write against your tables - again, meaningful names are very helpful for debugging.

I didn't put in data for the fare table, and the trip, vehicle and owner tables are only outlines - but you should be able to make a start with what I've shown here - you can drop back if you require further assistance/information.

CREATE TABLE trip  -- not all work done!
(
  trip_id SERIAL,
  trip_location_dep INTEGER, -- references location table
  trip_location_arr INTEGER, -- references location table
  trip_date DATE NOT NULL,
  trip_time TIME NOT NULL,
  trip_fare INTEGER -- same checks as for fare.
);

What you want are JOINING tables - also called Associative entities or simply many-to-many tables!

You could have one, for example, between trip and leg?

Call it trip_leg and have trip_id and leg_id as the two fields - you might also want an order field i.e. what might be A->B->C->D->E on one route, might be A->D->C->E on another - one-way streets are another issue - the vehicle can only be going only one way, despite being on different trips/routes.

1 trip can have many legs and 1 leg can be on many trips, for example, if you have a number of locations around a city, you may have partially overlapping routes depending on your setup.

Some of your vehicles might be more suitable for longer routes - a large bus for example, while some (tuk-tuks or smaller taxis) might be more locally based?

Many choices in designing your system will depend on your particular needs! Check out this page and that site in general - you can see what others have done when faced with a similar situation and you can customise them according to your needs!

CREATE TABLE vehicle 
(
  vehicle_id SERIAL,
  vehicle_owner INTEGER, -- references owner table (not shown)
  vehicle_driver INTEGER, -- references driver table (not shown) - may want different drivers even for same vehicle?
  vehicle_capacity
);


CREATE TABLE vehicle_owner
(
  owner_id SERIAL,
  owner_name VARCHAR(30),
  .. other fields
  ..
);

HTH, and best of luck.