Updating MySQL Efficiently, Question

[mididelight]

Hello,

So I am creating a program that allows the client to associate sales people to zip codes in the united states. A salesperson can be associated to more than one zip code.

I got a zip code database from the US census and I added a column called "salesperson_id" (this column will store the salespeoples' id...ex. 1,2,5). I also created a "salesperson" table to store the id and info of each sales person.

So I have done some associations for example:
Zip Code SalesPerson ID
--------- --------------
90210 2,3,5
90123 1,2,5

(you get the idea).

My confusion comes when I want to either remove a salesperson from a zip code, or add someone on. I do not want to mess up the other ID's if I am removing one and I also dont want to mess it up if I add ones on.

I understand how to do the add part:
SET salesperson_id = concat_ws(',', salesperson_id, '".$newID."')

Is this a valid way to do it?

Then how would I remove say, the the ID 5, from both zip codes in my example?

Your help is appreciated. I hope this wasnt too long or confusing.

[chazzy]

think about this structure:


sales_people
sales_person_id, other information

locations
zipcode, city_name, etc

sales_person_to_location_lookup
sales_person_id, zipcode

you shouldn't store multiple values in a single column. that doesn't work in searching for one thing. this design allows you to flexible add and remove zipcodes, sales people and their locations.

[mididelight]

oh, i like that idea , basically create a table that combines the two? that seems efficient. any drawbacks?

[chazzy]

you'll take a little bit of a hit on performance, but if you have to have 1 zip, multiple guys, 1 guy multiple zips, this is the only way it'll work accurately.

[mididelight]

with that setup, how do i insert multiple zip codes for one or more sales people in PHP?

[chazzy]

so let's say that $zipcode is a single value and $salespeople is an array of sales people ID's.

build your insert like this:


$sql = "INSERT INTO `your_table`(`zipcode`,`sales_person`) VALUES";
$i=0;
foreach($salespeople as $salesperson){
$sql .= "($zipcode,$salesperson)";
$sql .= ($i == length($salespeople)) ? "" : ",";
$i++;
}


you would do the same thing for the matrix. but I don't think you'd really be associating multiple zipcodes w/ multiple sales people in a single form - the logic on making those decisions would be very difficult to implement (i've done it before, users tried it and didn't like it so i switched back to 2 separate forms - 1 user, multiple items and 1 item multiple user formats.

[mididelight]

thanks for the code. i agree with your reasoning on having multiple zip codes to multiple users in one form. But, that kind of stinks, cause i would like the client to be able to associate multiple zip codes with multiple sales people. For example, so that 3 different zip codes could be assigned to 2 different sales people. so basically, the two sales people shared zip codes. Is that different than what you were saying before about using two forms?

[chazzy]

you can create it, but then how do you edit that user's zipcodes? it just becomes very cumbersome because you should only be able to edit one of:
1 user's zipcodes
1 zipcode's users
at a time
you really can't edit multiple user's zipcodes, as they shouldn't be restricted to the same, unless you bring in "zipcode groupings" that determine what group a zipcode belongs to and what users access those groups.

[mididelight]

i guess the problem wouldnt be having to edit zip codes. My UI setup for the client would be one select box to choose zip codes and one select box to choose the sales person. Then you click submit and then I want to have all the zip codes selected be paired with all the salespeople selected.

Then if the client would like to remove zip codes, I have another select box that allows them to delete zip codes from certain sales people.

I am just having difficulty coding how to pair multiple zip codes to multiple salespeople at the same time. So that all the salespeople selected are associated to the same selected zip codes. Does that make sense? I tried recoding your example with this:

$i=1;
foreach($salespeople as $salesperson){
foreach($zipcodes as $zipcode) {
$values .= "('$zipcode','$salesperson')";
$values .= ($i == $ziplength) ? "" : ",";
$i++;
}
}

But I think I am doing something wrong.

[chazzy]

well what is the output of that? what does $values look like when it iterates through?

[mididelight]

i changed the forms so that you can select many zip codes but only one salesperson. I showed the client and they were pleased. So I think everything is set for now. Thank you for your help. I appreciate it. If I have any more questions, I will repost to this thread.

[chazzy]

they typically are. you usually don't have more than 1 sales person covering an area, they compete with one another. if you want to make the argument "well what if you have a large city, like LA, New York, etc where you want to have multiple salesmen" you just have to remember that NYC has some areas where it is 1 building = 1 zipcode. probably similar w/ chicago and LA i would think.

[mididelight]

yes exactly, thanks again