Need help to design a table that can help finding the route / transshipment details for an address.

We have “Hub” and one Hub can have multiple “DeliveryPoints”. When we check one address our system tells what is the final “Hub” and “DeliveryPoint” that will be responsible to deliver the product. But there may be no direct transport available from "Source Hub" to "Destination Hub".

Sample data

Source  Destination  Route
------  -----------  ----------------------------------------------------
City-1  City-2       Direct  
City-1  City-3       First Transfer to City-2 and then City-3  
City-2  City-3       Direct  
City-4  City-5       First Transfer to City-1 and then City-2 then City-5  
City-4  City-1       Direct 


What will be the table design where we can maintain above "Routing Details" so that when our system knows "Sending from City-1 to City-3" it will tell to send to "City-2" first. And then in "City-2 Hub" when we see the "Product it shall say send directly to City-3".

The present database structure

Currently we have one table called TransitDays that contains the "Transit Days from a City to another City":

CREATE TABLE TransitDays (  

And another table named Mapping that retaines the ”City” served by the ”DeliveryPoint”, but this table does not keep the ”Middle Route”:

  • 1
    You may find of help some of the deliberations contained in these posts about a letter transportation business environment.
    – MDCCL
    Commented Sep 6, 2017 at 15:41
  • Thank @MDCCL for editing the question and pointer. That is a good topic to see, thanks for the link. In my case, I am thinking how can I store the route mapping at specific Hub where the product has to go from City-1 to City-2. And also there could be multiple Routes from City-A to City-B.
    – Prabhat
    Commented Sep 7, 2017 at 9:36


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.