php mysql list reps by state在rep表中没有多行

Question

I have a problem that I haven't been able to solve in an efficient way. I need to dynamically generate a list of reps for each of the 50 states in the USA. Each state has a variable number of reps that is equal to one or more and which will fluctuate. Each rep can potentially show up in more than one state. To make things more complex, several states are divided into non-standardized regions (e.g. "North", "Upstate", "Central", etc. but not consistent nomenclature across all states). The final product will produce a series of tables similar to what can be seen at https://www.compatico.com/rep-tables.php.

Right now, I'm envisioning two database tables:

**StateTable_example1**
state_id  |  state_name  |  state_reps
MI            Michigan       1,2,3,4
OH            Ohio           3,4,5,6,7
IN            Indiana        2,4,5

**RepTable_example1**
rep_id  |  rep_fname  |  replname  |  (more columns not relevant here)
1          John          Smith
2          Jane          Doe
etc.

One idea that I have to handle regional data is to tack on extra data in the StateTable.state_reps column, sorta like this:

**StateTable_example2**
state_id  |  state_name  |  state_reps
MI            Michigan       1-Upper Peninsula,2-Lower Peninsula,3-Western
OH            Ohio           3-Northern,4-Southern
IN            Indiana        2-Northern,4-Eastern,5-Western

That would allow one row in the RepTable per rep while still allowing for regional variations... which would be great for updating RepTable data for stuff like phone numbers, emails, fax numbers, etc... but I can't begin to wrap my head around how to write a single query in php/mysql to handle that efficiently.

My other idea is to add a column to the RepTable to handle region and then have multiple rows for each rep if they have a position in more than one state... like this:

**StateTable_example3**
state_id  |  state_name  |  state_reps
MI            Michigan       1,2,3
OH            Ohio           4,5
IN            Indiana        6,7,8

**RepTable_example3**
rep_id  |  rep_region  |  rep_fname  |  replname  |  (more columns not relevant here)
1          Upper Peninsula   John          Smith
2          Lower Peninsula   Jane          Doe
3          Western           Tom           Jones
4          Northern          Tom           Jones
5          Southern          Amy           Wong
6          Northern          Jane          Doe
7          Eastern           Amy           Wong
8          Western           Jim           Johnson

I think that the php/mysql statement for this method would be cleaner but then there's the question of updating rep information (which changes frequently) when each rep is represented by multiple rows.

I've thought about a third table, to map the reps to states, but I can't figure out how to do that in a way that allows for disparate regions for each rep (e.g., in the above data Amy Wong's region is "Southern" for Ohio but "Eastern" for Indiana).

Anyone know of a way to do this efficiently, without multiple calls to the database? Any help would be much appreciated!

*****EDIT**** The final output, while resembling the tables in the link above, will actually be used as input into another script with graphically maps the reps. The table data becomes tooltips. The actual final output will look like this: https://www.compatico.com/find-a-rep.php (but with more region data, as that's currently lacking).

*****EDIT_2***** I renamed my example tables to clarify.

Answer 1

Here's what you should have for tables:

Rep
---
rep_id
fname
lname  

State
----------
state_code
name

Region
----------
region_code
name
state_code (fk)

RepRegion
---------
region_code
rep_id

Just based on your report spec, a query like this should be pretty close to what you would need -- very standard joins and an order by to get things listed in the order you want.

The only trick to making this work, is that for each state where you need it, you should have either an 'ALL' or 'NONE' region, or you could include a tinyint flag named something like 'noRegion' which is 0 by default. In other words, you need at least one region for every state, even if the state has no regions. You only need an 'ALL/None' region if there are either no regions for the state, or there are regions and reps, but you also have reps who are not tied to a specific region.

For reps that span regions, states or any combination, you need to have a RepRegion row for that Rep.

Reps who are not part of a Region would be designated as such, by the fact that they are attached to one of these special "no region" regions.

Of course this is a result set, so your report assumes some procedural product to handle the format and understand when to add a new section header by state, or subheading by region.

At that point you can join from RepRegion to Rep & State, and you will easily create the report in your sample.

SELECT rr.*, rp.*, re.*, s.*
FROM RepRegion rr
JOIN Rep rp ON (rp.rep_id = rr.rep_id)
JOIN Region re ON (re.region_code = rr.region_code)
JOIN State s ON (s.state_code = re.state_code)
ORDER BY s.name, r.name, rp.lname, rp.fname