[jtrehern]

I am having some issues designing a database and I would like some input from people who have some experience with table design and table relationships. I am writing a database for a jail and this database will keep track of all inmate visitations that they are allowed to have while in jail. They are visited by various family members and friends who must go through an approval process.
The database must keep track of personal information pertaining to the visitors. Some examples of visitor data would be name, address, SSN, phone number, and so on. In addition, a record must be kept for each time the visitor calls and schedules a visit as well as if they showed up or not.
I also have to keep track of visit information. Examples are, date and time of visit, who showed up, what inmate they visited, and so on. Each inmate can have up to 6 visitors at each visit.
Now for the hard part. Each visit may have up to 6 different visitors. Each visitor may be approved to visit more than one inmate. I need to be able to keep track of who showed up for the visit and what inmates they can visit. I do not know how to accomplish this other than a one to many relationship. For example, one visitor to many visits but if I take that approach, I would have to have multiple fields in my visit table.
Does anyone have another approach I could use to accomplish this?

[macosxnerd101]

Topic split. Please avoid hijacking other members' threads.

[jtrehern]

I am having some issues designing a database and I would like some input from people who have some experience with table design and table relationships. I am writing a database for a jail and this database will keep track of all inmate visitations that they are allowed to have while in jail. They are visited by various family members and friends who must go through an approval process.
The database must keep track of personal information pertaining to the visitors. Some examples of visitor data would be name, address, SSN, phone number, and so on. In addition, a record must be kept for each time the visitor calls and schedules a visit as well as if they showed up or not.
I also have to keep track of visit information. Examples are, date and time of visit, who showed up, what inmate they visited, and so on. Each inmate can have up to 6 visitors at each visit.
Now for the hard part. Each visit may have up to 6 different visitors. Each visitor may be approved to visit more than one inmate. I need to be able to keep track of who showed up for the visit and what inmates they can visit. I do not know how to accomplish this other than a one to many relationship. For example, one visitor to many visits but if I take that approach, I would have to have multiple fields in my visit table.
Does anyone have another approach I could use to accomplish this?

[macosxnerd101]

Duplicate topics merged. Please avoid duplicate posting.

[Ionut]

Well, I think you need one or more intermediate tables.
Between Visit and Visitor is a many to many relationship, so a table like
VisitorXVisit(Id, VisitId, VisitorId) would solve some of the problems.

Quote

Each inmate can have up to 6 visitors at each visit.
Each visit may have up to 6 different visitors (i think for an inmate)

Taking this into consideration, you might add a column to VisitorXVisit or define another table VisitXInmate(id, VisitId, InamateId). With inner join between this tables you get the information you need.

Ionut