I am in the process of creating a system that could identify shipping companies (Fedex, UPS, etc.) from a given pincode (zip code) for a e-commerce platform.

The shipping companies will provide a list of pincodes where they provide pickup and delivery. So when we get an order for a product, with the pincode of the seller and with pincode of the buyer I need to get a list of shipping companies who provides both these services (i.e. pickup and delivery).

My initial plan is to create a table structure like the following one:

Table shipping_companies

id | company
1  | Fedex
2  | UPS
3  | DHL

Table pincodes

id | pincode | shipping_ids_pickup | shipping_ids_delivery
1  | 263152  |         1,2         |       2,3

The pincodes table will have the pincodes and will have two columns one for pickup and another for delivery.

So when a user purchases a product I need to find a shipping company that has pickup from the sellers location and also has delivery at buyers location.

If we are adding another shipping company in the future I can update the shipping_ids_pickup, shipping_ids_delivery for a pincode by appending the id.

Can someone suggest a better solution. Is there any issue with current approach.


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.