4 Replies - 2801 Views - Last Post: 27 July 2010 - 03:38 PM Rate Topic: -----

#1 jtrehern  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 10-April 09

Database Design for a Prison System

Posted 27 July 2010 - 11:40 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: Database Design for a Prison System

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10596
  • View blog
  • Posts: 39,258
  • Joined: 27-December 08

Re: Database Design for a Prison System

Posted 27 July 2010 - 11:42 AM

Topic split. Please avoid hijacking other members' threads.
Was This Post Helpful? 0
  • +
  • -

#3 jtrehern  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 10
  • Joined: 10-April 09

Re: Database Design for a Prison System

Posted 27 July 2010 - 11:52 AM

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?
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10596
  • View blog
  • Posts: 39,258
  • Joined: 27-December 08

Re: Database Design for a Prison System

Posted 27 July 2010 - 11:56 AM

Duplicate topics merged. Please avoid duplicate posting.
Was This Post Helpful? 0
  • +
  • -

#5 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Database Design for a Prison System

Posted 27 July 2010 - 03:38 PM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1