0

I am trying to implement a shipping and tracking solution.

There are TRUCKS that move PRODUCTS from a CITY to another CITY via designated PATHS on specific SCHEDULES. The TRUCKS transport the PRODUCTS via CONTAINERS. These TRUCKS are instructed via JOBS, like pick_up, transfer, drop_off.

The problem that I am having is when product_1 needs to be moved by truck_1 via a pick_up JOB from city_A to city_C via city_B. At the same time, there is product_2 being moved by truck_2 via a JOB of pick_up from city_C to city_A via city_B. Both trucks can transfer (JOB) their 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 drop_off (JOB).

So I have the following tables:

  • TRUCK (truck_id, truck_code, ...)
  • PRODUCT (product_id, product_code, product_name, ...)
  • CONTAINER (container_id, container_desc, ...)
  • CITY (city_id, city_name, city_desc, ...)
  • PATH (path_id, from_city_id, to_city_id, ...)
  • SCHEDULE (schedule_id, schedule_name, schedule_desc, start_time, end_time, ...)
  • TRANSACTION (transaction_id, transaction_name, transaction_desc, ...)

How could I model the above scenario where the CONTAINERS are transferred between the TRUCKS?

emily_bma
  • 301
  • 1
  • 15
  • I am not sure how anyone here would know, as this is completely domain-dependent. – OldProgrammer Jan 22 '15 at 21:30
  • Well, after looking at this problem more, one begins seeings patterns, e.g. I can see this problem similar to that of a UPS/FEDEX scenario, i.e. if I order something from NY and I live in CA, UPS will deliver it. Does anyone know where I can find models that resemble that of a shipping/transportation scenario? Would you agree on my assessment? – emily_bma Jan 22 '15 at 21:33
  • If I understand correct, you want us to design the data model with the entities as described above. If you have issues creating a complex model - which this one seems to be - just start with some entities only, then expand. I guess you are missing some entities, like for example : where are you storing how many products are stored in which containers ? – tvCa Jan 22 '15 at 21:54
  • @tvCa - I don't want you to design the data model. I would say I have the majority of it already designed as noted. I am not designing a full ERP solution, simply adding to it, thus I do not need to keep track of things like order details, shipping details, invoicing, etc. – emily_bma Jan 22 '15 at 22:13
  • OK, please clarify the exact question then. Not for me, but for everybody. – tvCa Jan 22 '15 at 22:28
  • @tvCa - I just updated my question. Hopefully it is more succinct now, thanks! – emily_bma Jan 22 '15 at 22:34

1 Answers1

1

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). The base table predicates are given by the designer:

// 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. And a query result also holds the rows that satisfy its predicate.

(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.)

philipxy
  • 14,867
  • 6
  • 39
  • 83