SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Should I be putting comma separated lists in fields?

    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:

    Code:
    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. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,082
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ggeiger View Post
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?:

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

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,823
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ggeiger View Post
    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?:

    Code:
    agent_id     state
    ------------------
    1            IL
    1            AZ
    1            NY
    1            GA
    2            MA
    2            HI
    3            CT
    3            DE
    3            ME
    Yes, you nailed it exactly.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •