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.

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.

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.

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

Thank you both very much! Your advice/info has helped put me on the right track.