I have a table for insurance agents who need to handle applications from a web form. Depending on the state the user enters in the form, an email would notify a particular agent. One agent handles multiple states. So my table is looking like this:
id state name email
--------------------------------------------------------
1 IL,MI,CT,KY,MS,DE,VT Bob bob@email.com
2 NV,NM,NE Glenn glenn@email.com
3 OH,NC,MD,OR,AR,DC Jeff jeff@email.com
I suppose I would first find agents that have a not empty state, then see if the state I’m looking for is in the list for that agent, if not move on to the next record. I’m not sure if this is a good way to accomplish this, neither do I know exactly how to do it, so I thought to pass it by the trusted forum first. Any thoughts?
So with your current design, to find an agent that can work a file, a query would look like this:
select
name, email, id
from
agents
where
state like '%$myState%'
This can be a lengthy query, if your agent list gets long. Proper relational design would dictate that you’d remove the state column from agents, and make a new table.
agents
agent_id
fname
lname
email
agent_states
agent_id
state
where agents to agent_states is a 1 to many relationship, multiple states are entered in to the table for each id, if they will work that state, your sql would then look like this and perform much better:
select
a.name, a.email, a.agent_id
from
agents a
inner join agent_states as on a.agent_id = as.agent_id
where
as.state = '$myState'