Take the 2-minute tour ×

The premise of the project is this: A mailed package is assigned to particular courier based on its pickup and dropoff locations. It could be FROM a specific city, a state, a country, or a region (a group of countries, set up in another table) and then TO any of those choice.

At this moment, I have the package information in a table:

    package_id, pickup_city, pickup_state, pickup_country, 
dropoff_city, dropoff_state, dropoff_country.

There's also a cities table, a countries table, a states table, and a regions table.

The way I set up my "rules" table is to have a pickup_type (city,state,country,region), pickup_country, pickup_location.... and dropoffs of the same.

My problems comes in when I try to determine which rule is assigned to each package.

My initial thought is to get the package's pickup city... loop through the "city" type rules... if there aren't any matches, loop through the "state" rules, then "country" rules, then "regions". If it matches a rule, then it loops through all the dropoff rules to see if there's a match THERE.

So what I have now is a huge block of code with a bunch of "if/else" loops and I'm thinking it could be done at least a little simpler.

I'm just looking for some direction if you've ever had a project like this. Am I starting the right way? Do I need to restructure my entire schema? Is there an easier way to match the "rules" with the package's locations?

Thanks for any help you can give.

edit for clarification, let me give a scenario... A package is going from Los Angeles to Tokyo, Japan. In the "rules" table, there are these rules:

+ name          +  pickup_type  + pickup_country + pickup_location + dropoff_type + dropoff_country + dropoff_location +
+ US to Asia    + country       + US             + null            + region       + null            + asia_region      +
+ US to Japan   + country       + US             + null            + country      + Japan           + null             +
+ LA to Japan   + city          + US             + Los Angeles     + country      + Japan           + null             +
+ LA to London  + city          + US             + Los Angeles     + city         + UK              + London           +

So the third rule is the one that's applicable. My current process loops through the pickup cities to find a match (#3 and #4), then loop through those results on the dropoff city... and if it can't find any match, tries the states, then country, then region.

And just now, I was thinking, "What is it's LA to Australia?"... my way would find LA, do all the work to loop through the dropoff locations, and then have to back-track to the pickup location when it didn't find anything.

I'm sure this is all clear as mud. Thanks for any advice.

edit 2
@shadyyx I've got the rules table shown, and the fields for the package information are at the top... and the rules table also has an ID field, which is what I want to return.

Theres also a "regions" table that is key/value pairs:

+ region_id + country_id +
+ 1         + JP         +
+ 1         + CN         +
+ 2         + US         +
+ 2         + CA         +

In this scenario, Region 1 is an Asia region with Japan and China, and Region 2 is North Amaerica with US and Canada

So my process becomes 1) check if the pickup city is part of a rule, 2) if not, check if the pickup state is part of a rule, 3) if not, check if the pickup country is part of a rule, and finally 4) if not, see if the country is listed in the "regions" table and get the region_id. Once I find that rule(s), then look through the dropoff rules and find a match there. I really do appreciate the help, and I'll try to provide as much as info as needed. Thanks again.


After some more research, I found one potential solution by doing multiple SQL queries, then put the results for the pickup and dropoff rules into arrays, then comparing them. So to check the cities:

SELECT id FROM rules WHERE pickup_type = 'city' AND pickup_country = '$pu_country' AND pickup_location = '$pu_city';
// put the results into a flat array, then check the countries
SELECT id FROM rules WHERE pickup_type = 'country' AND pickup_country = '$pu_country';
// add those results to the same array... and so on.

Then do the same for the Dropoff rules. Then run array_intersect() on the two arrays to find the matching ones. The only problem is if there is a US->UK rule and a US->London rule, how do you figure which is the top rule? I guess that's where @pjskeptic's idea would come in.

share|improve this question
Hi Terry, could you clarify what you are actually wanting to return from the rules table? In the example above it sounded like you already knew the 'LA' and 'Japan' part that you were using to lookup 'something' in your rules table. I couldn't understand from what you put why you couldn't just lookup ($from . ' to ' . $to) directly and skip out all the loops altogether. Perhaps I am missing something? –  eyaka1 May 4 '11 at 13:12
I suppose that all of these rules are stored in one table in the database and the rule determines whether a package could be delivered from pickup to dropoff. Therefore I'd recommend using one SQL query with complex joins and conditions that will return 1 if the rule was found or false when no rule was found. No need of bunch of if/else would be needed... If you provide us some DB tables schema I'll try to create the query for You... –  shadyyx May 4 '11 at 13:23
@eyaka1 I'm trying to return the ID from rules table that matches the pickup and dropoff location. The reason for the loops...if, in my scenario, the picxup was Boise, Idaho and dropoff was Osaka, Japan, I would need to make sure that Boise was not in a cities pickup rule, and then backtrack to use the US pickup rule... and the generic Japan dropoff rule. –  TerryMatula May 4 '11 at 13:45
I'd also like to make clear, this schema is not set in stone... and if there's a better way to have it setup, I'd be able to do that. –  TerryMatula May 4 '11 at 14:07
Is the "courier" a person (Joe, Tess, etc.), or a carrier (UPS, FedEx, etc.)? –  jefflunt May 5 '11 at 2:09
show 1 more comment

2 Answers

up vote 1 down vote accepted

Add a field called "type" that ranks your dropoff and pickup types where lower numbers represent better (more precise) options:

  1. city/city
  2. city/country
  3. country/city
  4. country/country
  5. city/region
  6. region/city
  7. country/region
  8. region/country
  9. region/region

Since you're storing tables of cities, countries, states, and regions, you can use their ids in your rules table. Modify your rules table to have a schema similar to the following

  • name
  • type (from above)
  • pickup_type
  • pickup_city_id
  • pickup_state_id
  • pickup_country_id
  • pickup_region_id
  • drop_type
  • drop_city_id
  • drop_state_id
  • drop_country_id
  • drop_region_id

Now, if you know your pickup and destination cities, you should be able to load their state, country, and region ids from your other tables. Then, you can perform the following query:

 SELECT * FROM rules WHERE ((pickup_type = 'city' && pickup_city_id = '$pickup_city_id') OR
(pickup_type = 'state' && pickup_state_id = '$pickup_state_id') OR
(pickup_type = 'country' && pickup_country_id = '$pickup_country_id') OR
(pickup_type = 'region' && pickup_region_id = '$pickup_region_id')) AND ((drop_type = 'city' && drop_city_id = '$drop_city_id') OR
(drop_type = 'state' && drop_state_id = '$drop_state_id') OR
(drop_type = 'country' && drop_country_id = '$drop_country_id') OR
(drop_type = 'region' && drop_region_id = '$drop_region_id')) ORDER BY type ASC

That query will look for rules that match your dropoff city, state, country, or region and your pickup city, state, country, or region. Because you're sorting by type, the first result in the set should be the most convenient courier.

share|improve this answer
I'm not sure I totally understand. What would go in the variables $pickup and $dropoff? If it's the City, then there would still need multiple queries if that City isn't listed in any rules. Could you explain further? Thanks for the input! –  TerryMatula May 4 '11 at 16:48
Okay, so you'll have to add that to the query. I'm posting an edit that takes that into consideration. –  psparrow May 5 '11 at 0:38
That looks good. I'll try it out. Thanks! The one problem I may run into is that certain countries might be in multiple regions... and I'll need to figure out which is the most relevant region for the delivery. But I should be able to figure that out. –  TerryMatula May 5 '11 at 16:21
Works perfect. Thanks again! –  TerryMatula May 5 '11 at 17:52
add comment

Perhaps I'm misunderstanding, but I have a question tied to my answer:

What's up with the complicated set of rules? Shouldn't you track pickups and drop offs by address (which encapsulates the street number, city, state, country, and region), and figure it out from there? If you have a pickup in Australia and a drop off in England, the same courier isn't going to handle both sides of that (unless they're traveling all the way with the package). Maybe that's what you intend, whereas I'm thinking you're looking for a solution similar to UPS/FedEx, etc.

With the package companies, cities are broken into routes, couriers are assigned one or more routes, and the courier who picks stuff up or drops it off depends on whether or not it's on their route. This applies even if a package is going just from one end of a city to the other. Only if a package is to be picked up and dropped off within the same route would it be handled by a single courier.

If one courier is out sick (or for some other reason cannot cover their route for the day), their route is simply assigned to another courier, and similarly a courier can have an arbitrary number of routes assigned to them, and shuffled around at any time.

share|improve this answer
I'll try not to divulge too much company information... but basically, the "couriers" can either be a single person (or company, like DHL) who pickup and deliver the package themselves... OR they make other arrangements, using 3rd party companies, at origin and destination. Either way, we're dealing with a single point of contact for the entire delivery. And we use different people/companies depending on pickup/delivery location. –  TerryMatula May 5 '11 at 16:20
So, it's like you're trying to setup logic such that you can most efficiently pick a courier to deliver a given package, as opposed to trying to build a system like UPS, where you're trying to figure out courier assignments. Correct? If so, then I guess I don't know how to help here. I thought you were trying to build a UPS-like system. –  jefflunt May 5 '11 at 17:17
add comment

Your Answer


