Modelling a database for a travelling business that involves Vehicules, Routes, Subroutes and Prices I am working on travelling app, where users can book online vehicles to travel.


On the customer side I would like this flow:


Users have to select a from location and a to location
Enter how many seats they require
Select a travel date 
Search for a ride/vehicle.



Which means, I need to search all the vehicles/rides whose available seats are equal or more than the user wants, and who has this route: from location - to location.


And on the merchant/vehicle owner side I would like this flow:


Owner/merchant selects a vehicle
Create a route by selecting a from location --> and a to location
Set price for that trip.



An owner can have many vehicles and each vehicle can take different routes and each route can have a different price. Also whether the route is back and forth.

However, please consider the example below:

A vehicle has a route from A to D and it has a price for that trip. i.e., 

A-----------------D     Price 100

But it can also have sub routes within that route. Example from A to B or B to C, e.t.c.

A-----B------------     Price 25

A-----------C------     Price 75

------B-----C------     Price 25

------B-----------D     Price 75

How can I design a database model for such a relationship between Vehicle and Routes, where a vehicle can have multiple routes and each route can have multiple sub routes so that I can search for all the vehicle routes? If it helps I am using Django. Thank you.
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 &amp;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
&amp;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 &gt; 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 &lt;&gt; 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.