1

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

Question

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 (  
    TransitDaysId...
    FromCityId... 
    ToCityId...
    TransitDays...
);

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

CREATE TABLE Mapping (
    Mapping...
    FromCityId...
    ToCityId...
    DeliveryPointId...
);
MDCCL
  • 8,520
  • 3
  • 30
  • 61
Prabhat
  • 317
  • 2
  • 4
  • 15
  • 1
    You may find of help some of the deliberations contained in these posts about a letter transportation business environment. – MDCCL Sep 06 '17 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 Sep 07 '17 at 09:36

0 Answers0