I am trying to implement a shipping and tracking solution.
TRUCKS that move
PRODUCTS from a
CITY to another
CITY via designated
PATHS on specific
TRUCKS transport the
TRUCKS are instructed via
The problem that I am having is when
product_1 needs to be moved by
truck_1 via a
city_B. At the same time, there is
product_2 being moved by
truck_2 via a
containers (and essentially the
product and just head back to their origin
city, i.e. for
truck_1, back to
city_A and for
truck_2, back to
city_C via a command of
So I have the following tables:
How could I model the above scenario where the
CONTAINERS are transferred between the
Presumably a truck and/or trucker has an assignment involving going through a sequence of events that including following a path and making deliveries and transactions, etc. Presumably a job is such an event, of which there are several kinds, eg pickup, transfer and dropoff.
The tables in a relational database describe the state of an application. Each table has an associated fill-in-the-(named-)blanks statement (predicate).
// truck [truck_id] has code [truck_code] and ... TRUCK (truck_id, truck_code, ...) // product [product_id] has code [product_code] and name [product_name] ... PRODUCT (product_id, product_code, product_name, ...)
(A predicate characterizes an application relationship, aka relation, represented by a table, aka relation, hence "the relatonal model".)
The parameters of the predicate are the columns of the table. When you supply values for each parameter you get a statement (proposition) that is true or false about your application. A row of values for columns gives such values for each named blank. The rows that make a table's predicate true go in the table. The rows that make if false stay out. That is how the database state describes the the application situation. You have to know the tables' statements in order to read or query the database to find out per its rows what is true and false about a situation and to update the database by putting exactly the rows that make true propositions into it after observing the situation.
Every query also has a predicate built from the predicates of its tables. The JOIN of two tables gives the rows that satisfy the AND of their predicates, UNION the OR, etc.
(Constraints are irrrelvant to this; they just collectively describe the database states that can arise given the predicates and the applcation states that can arise.)
You need to decide on sufficient predicates to be able to fully describe the the stituations of your application. This includes abstract things like routes and transactions and events and schedules and assignments etc. (Once we have sufficitent predicates/tables we improve them via techniques like normalization.)
When there can be different kinds of things we talk about supertypes and subtypes and see predicates like (I'll use "job" which I take to be an event):
// job [job_id] for trucker [trucker_id] is ... stuff about all jobs ... JOB(job_id, trucker_id...) // job [job_id] is a pickup with ... stuff about pickups ... PICKUP(job_id, container_id...) // job [job_id] is a transfer with ... stuff about transfers TRANSFER(job_id,...) ...
(You may or may not have a different or additional notion of transfer as an event with two or more associated containers, etc.) (Search "subtypes". Eg.)