Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Can't wrap my mind around what is seemingly a simple concept...

    We're making a database of our client visit logs. One of our tables called "Cases" contains a field for "Visitors". What we are trying to convey with this field is which of us in the office went on dispatch for this case. So far so good. What is blowing my mind is that for most cases, we dispatch two or more people for each case.

    Should we just use special character delimiting so that the Visitors field is like "mike,jon,sarah" ? It's completely variable how many people could go on a case, so it would be pointless to create extra columns like Visitor1, Visitor2, Visitor3, because potentially we could have a case where 10 of us go, or only 1 of us goes, who knows. Ideally we'd like to have a lookup table such as:

    1 | mike
    2 | jon
    3 | sarah

    so that we can easily just do a select all from cases where visitor is 1 in order to get all of the visits mike has done.

    I've googled around for this and found several cases that are similar, but because it's not exactly the same, I'm having a hard time making that leap of connecting the dots to make it fit our case.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Do not use a comma-separated list.
    Do not use multiple fields.
    Use a subtable instead. Call it, for instance, "CaseVisitors", and store the CaseID and the PersonID. A table such as this implements the many-to-many relationship that you need.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2010
    Posts
    22
    Thanks a lot! That really narrows down the general idea. I was admittedly trying to keep it to as few tables as possible, but it's clear now that we're going to need such a CaseVisitors table.

    The remaining question is what is the preferable way to connect the Case table to the CaseVisitors table? I've attached a picture I drew in Paint to get a visualization of the tables. Would the CaseVisitors "CaseID" field be a foreign key relating the the Case table's "CaseID" field? I feel like this is right, but would just like confirmation and additional advice before taking a crack at this.
    Attached Thumbnails Attached Thumbnails casevisitors.jpg  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what you have is in effect an intersection table for CaseVisitors, its a way of implementing a many to many design

    ..so you have
    a table for Visitors/employees whatever you call it
    a table for visits
    a table for people on that visit. would include the PK for the visit and PK for an employee, you can have multiple visits, multiple employees but only one visit+employee combination

    What is the intersection Table ? - HTML Forums - Free Webmaster Forums and Help Forums
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2010
    Posts
    22
    Thank you both very much! Your advice/info has helped put me on the right track.

Posting Permissions

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