Should I be putting comma separated lists in fields?

Databases
1

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?

2

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'
3

Excellent. I’ll try the better approach first. How else am I going to get better at this? Thank you very much!

To be sure, would my agent_state table look like this?:


agent_id     state
------------------
1            IL
1            AZ
1            NY
1            GA
2            MA
2            HI
3            CT
3            DE
3            ME
4

Yes, you nailed it exactly.