Prev: Append Query and Autonumbering
Next: Conversion To Acc2007 or beyond Suggestions ?
From: Lee Ann on 24 Feb 2010 10:21
I'm looking into correcting a previously developed database that was
originally done and works like an excel spreadsheet (all fields are currently
in one table). In looking at it, there appear to be many many-to-many
relationships involved. This is used to keep records relating to force used
against suspects by police. In one incident, there could be 1 or more
suspects involved, 1 or more officers involved, 1 or more types of force
involved (possibly different force by each officer involved), none/one/more
charges placed on the suspect, and a final disposition on each suspect
involved (jail, hospital, etc). To further confuse the scenario, the shift
that the officer is on at the time of the incident is captured - however if
the same officer is involved in another incident down the road, he/she may be
on a different shift.

I know I will need a linking table to establish the many to many
relationships and am unsure how many tables are required and which fields
should be contained in them. I currently have the following tables relating
to the above information:

TblSuspect
SuspectID (PK)
(identifying information fields)

TblOfficer
OfficerBadgeNumber (PK)
(Officer's first and last name fields)

TblTypeofForce
TypeofForceID (PK)
TypeofForce

TblCharges
ChargesID (PK)
Charges

TblShift
ShiftID (PK)
Shift

TblDisposition
Disposition(PK)
Disposition

I have a main incident table containing information related to the incident:

TblIncident
IncidentID (PK)
CaseNumber
DateandTimeofIncident
NatureofIncident
StreetAddress
City
AreaofIncident
LocationTypeID (FK)
Summary
OfficerBadgeNumber (FK)
SuspectID (FK)

Thanks in advance.




From: BruceM via AccessMonster.com on 24 Feb 2010 10:55
I think Disposition is an attribute of Incident. There can be a lookup table
for dispositions, but no need I can see for a junction table.

From what I can see, there would be junction tables for:

IncidentSuspect
IncidentOfficer
IncidentCharges

Since the type of force used is specific to an officer at an incident, the
ForceUsed table would be related to the IncidentOfficer table. The Shift
could be a linking field to a Shift table, or you could just store the value
(1st, 2nd, etc.). Here is an idea of what the IncidentOfficer table could
look like:

tblIncidentOfficer
IO_ID (primary key, or PK)
IncidentID (link to tblIncident)
OfficerID (link to tblOfficer)
Shift

Here is a listing of types of force:

tblForce
ForceID (PK)
Description

Here is the Force Used listing (also a junction table):

tblForceUsed
ForceUsedID (PK)
IO_ID (link to tblIncidentOfficer)
ForceID (link to tblForce)
Comments

Incident is the top-level table, so it does not contain foreign key fields
for Officer and Suspect. One incident: many officers and one officer: many
incidents, so there is a many-to-many relationship between officers and
incidents. The foreign key (linking) field is in the table on the many side
of the relationship (the junction table in the case of many-to-many). I'm
not sure what LocationTypeID is. However, I suspect it would be stored as an
attribute of the Incident: one LocationType could be in many incidents, so it
would be a foreign key field in the top-level table. One Incident would
involve just one Location Type (I suppose), in which case there is no need to
resolve a many-to-many relationship.

Lee Ann wrote:
>I'm looking into correcting a previously developed database that was
>originally done and works like an excel spreadsheet (all fields are currently
>in one table). In looking at it, there appear to be many many-to-many
>relationships involved. This is used to keep records relating to force used
>against suspects by police. In one incident, there could be 1 or more
>suspects involved, 1 or more officers involved, 1 or more types of force
>involved (possibly different force by each officer involved), none/one/more
>charges placed on the suspect, and a final disposition on each suspect
>involved (jail, hospital, etc). To further confuse the scenario, the shift
>that the officer is on at the time of the incident is captured - however if
>the same officer is involved in another incident down the road, he/she may be
>on a different shift.
>
>I know I will need a linking table to establish the many to many
>relationships and am unsure how many tables are required and which fields
>should be contained in them. I currently have the following tables relating
>to the above information:
>
>TblSuspect
>SuspectID (PK)
>(identifying information fields)
>
>TblOfficer
>OfficerBadgeNumber (PK)
>(Officer's first and last name fields)
>
>TblTypeofForce
>TypeofForceID (PK)
>TypeofForce
>
>TblCharges
>ChargesID (PK)
>Charges
>
>TblShift
>ShiftID (PK)
>Shift
>
>TblDisposition
>Disposition(PK)
>Disposition
>
>I have a main incident table containing information related to the incident:
>
>TblIncident
>IncidentID (PK)
>CaseNumber
>DateandTimeofIncident
>NatureofIncident
>StreetAddress
>City
>AreaofIncident
>LocationTypeID (FK)
>Summary
>OfficerBadgeNumber (FK)
>SuspectID (FK)
>
>Thanks in advance.

--
Message posted via http://www.accessmonster.com

From: Daryl S on 24 Feb 2010 11:11
Lee Ann -

You have a great start with the separate tables for the officers, suspects,
types of force, charges, and shifts.

You will need to break down the Incident table a little more, since you can
have multiple officers or suspects involved. So here goes:

TblIncident
IncidentID (PK)
CaseNumber
DateandTimeofIncident
NatureofIncident
StreetAddress
City
AreaofIncident
LocationTypeID (FK)
Summary

tblIncidentOfficers
IncidentOfficerID (PK)
IncidendID (FK)
OfficerBadgeNumber (FK)
OfficerShift (FK)

tblIncidentSuspects
IncidentSuspectID (PK)
IncidentID (FK)
SuspectID (FK)
FinalDisposition (FK) 'this assumes only one final disposition per suspect

tblIncidentOfficerForceUsed
IncidentOfficerForceID (PK)
IncidentOfficerID (PK)
SuspectForceUsedOn (PK) ' suspectID

tblIncidentSuspectCharges
IncidentSuspectChargeID (PK)
IncidentSuspectID (FK)
Charges (FK)
OutcomeOfCharges???

--
Daryl S


"Lee Ann" wrote:

> I'm looking into correcting a previously developed database that was
> originally done and works like an excel spreadsheet (all fields are currently
> in one table). In looking at it, there appear to be many many-to-many
> relationships involved. This is used to keep records relating to force used
> against suspects by police. In one incident, there could be 1 or more
> suspects involved, 1 or more officers involved, 1 or more types of force
> involved (possibly different force by each officer involved), none/one/more
> charges placed on the suspect, and a final disposition on each suspect
> involved (jail, hospital, etc). To further confuse the scenario, the shift
> that the officer is on at the time of the incident is captured - however if
> the same officer is involved in another incident down the road, he/she may be
> on a different shift.
>
> I know I will need a linking table to establish the many to many
> relationships and am unsure how many tables are required and which fields
> should be contained in them. I currently have the following tables relating
> to the above information:
>
> TblSuspect
> SuspectID (PK)
> (identifying information fields)
>
> TblOfficer
> OfficerBadgeNumber (PK)
> (Officer's first and last name fields)
>
> TblTypeofForce
> TypeofForceID (PK)
> TypeofForce
>
> TblCharges
> ChargesID (PK)
> Charges
>
> TblShift
> ShiftID (PK)
> Shift
>
> TblDisposition
> Disposition(PK)
> Disposition
>
> I have a main incident table containing information related to the incident:
>
> TblIncident
> IncidentID (PK)
> CaseNumber
> DateandTimeofIncident
> NatureofIncident
> StreetAddress
> City
> AreaofIncident
> LocationTypeID (FK)
> Summary
> OfficerBadgeNumber (FK)
> SuspectID (FK)
>
> Thanks in advance.
>
>
>
>
From: Lee Ann on 24 Feb 2010 11:56
Thanks Daryl and Bruce for your quick response(s).

For Bruce, would your comment on the disposition still be true if there is
more than one suspect, both of them having a different disposition (i.e., two
suspects with one going to the hospital and one going to jail) within the
same incident?

Correct - LocationTypeID is the type of locale where the incident occurs
(bar, street, residence, etc.) and there will only be one location per
incident.

"BruceM via AccessMonster.com" wrote:

> I think Disposition is an attribute of Incident. There can be a lookup table
> for dispositions, but no need I can see for a junction table.
>
> From what I can see, there would be junction tables for:
>
> IncidentSuspect
> IncidentOfficer
> IncidentCharges
>
> Since the type of force used is specific to an officer at an incident, the
> ForceUsed table would be related to the IncidentOfficer table. The Shift
> could be a linking field to a Shift table, or you could just store the value
> (1st, 2nd, etc.). Here is an idea of what the IncidentOfficer table could
> look like:
>
> tblIncidentOfficer
> IO_ID (primary key, or PK)
> IncidentID (link to tblIncident)
> OfficerID (link to tblOfficer)
> Shift
>
> Here is a listing of types of force:
>
> tblForce
> ForceID (PK)
> Description
>
> Here is the Force Used listing (also a junction table):
>
> tblForceUsed
> ForceUsedID (PK)
> IO_ID (link to tblIncidentOfficer)
> ForceID (link to tblForce)
> Comments
>
> Incident is the top-level table, so it does not contain foreign key fields
> for Officer and Suspect. One incident: many officers and one officer: many
> incidents, so there is a many-to-many relationship between officers and
> incidents. The foreign key (linking) field is in the table on the many side
> of the relationship (the junction table in the case of many-to-many). I'm
> not sure what LocationTypeID is. However, I suspect it would be stored as an
> attribute of the Incident: one LocationType could be in many incidents, so it
> would be a foreign key field in the top-level table. One Incident would
> involve just one Location Type (I suppose), in which case there is no need to
> resolve a many-to-many relationship.
>
> Lee Ann wrote:
> >I'm looking into correcting a previously developed database that was
> >originally done and works like an excel spreadsheet (all fields are currently
> >in one table). In looking at it, there appear to be many many-to-many
> >relationships involved. This is used to keep records relating to force used
> >against suspects by police. In one incident, there could be 1 or more
> >suspects involved, 1 or more officers involved, 1 or more types of force
> >involved (possibly different force by each officer involved), none/one/more
> >charges placed on the suspect, and a final disposition on each suspect
> >involved (jail, hospital, etc). To further confuse the scenario, the shift
> >that the officer is on at the time of the incident is captured - however if
> >the same officer is involved in another incident down the road, he/she may be
> >on a different shift.
> >
> >I know I will need a linking table to establish the many to many
> >relationships and am unsure how many tables are required and which fields
> >should be contained in them. I currently have the following tables relating
> >to the above information:
> >
> >TblSuspect
> >SuspectID (PK)
> >(identifying information fields)
> >
> >TblOfficer
> >OfficerBadgeNumber (PK)
> >(Officer's first and last name fields)
> >
> >TblTypeofForce
> >TypeofForceID (PK)
> >TypeofForce
> >
> >TblCharges
> >ChargesID (PK)
> >Charges
> >
> >TblShift
> >ShiftID (PK)
> >Shift
> >
> >TblDisposition
> >Disposition(PK)
> >Disposition
> >
> >I have a main incident table containing information related to the incident:
> >
> >TblIncident
> >IncidentID (PK)
> >CaseNumber
> >DateandTimeofIncident
> >NatureofIncident
> >StreetAddress
> >City
> >AreaofIncident
> >LocationTypeID (FK)
> >Summary
> >OfficerBadgeNumber (FK)
> >SuspectID (FK)
> >
> >Thanks in advance.
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: Fred on 24 Feb 2010 12:52
I suspect that there might be one more more element to be addressed in the
above plans. In your "use of force" recordkeeping requirement, do you need
to record the suspect that it was used on or just the incident where it was
used? I think that all of the responses wer based on the latter.

One thing that I find useful for designing/analyzing structures covering M:M
relationships is to consider the junction table to be a table of entities
rathr than a junction table. For example, considering the
tblIncidentSuspect table to be not a junction table between Insidents and
Suspects but rather a table of "Instances of a Suspect Being Involved in an
Incident". Since yours is unusually complicated in this respect, this might
be helpful.
 |  Next  |  Last
Pages: 1 2
Prev: Append Query and Autonumbering
Next: Conversion To Acc2007 or beyond Suggestions ?