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.