Database Design
by Rick Curtis
The Tables
The core table of the database is Incident. This tracks the bulk of information
submitting about a particular incident. Each field holds a single piece of
information about the accident. This is standard database design practice referred
to as Normalization. However, in some cases there may be multiple pieces of
information to record that is all considered to fall under one category. For
example, under injuries there might be multiple parts of the body that were
injured. There are several ways to deal with this.
| Solution |
Pros |
Cons |
| Add one field for each item |
Keeps all the data in one table. |
Makes the table extremely large. Adding a new piece of
information to be stored means adding an entirely new field. If there
are multiple copies of the database in use synchronization becomes extremely
difficult. Many database fields will be empty. Poor database design practice.
Less efficient database storage. |
| Allow multiple pieces of data to be entered in each field. |
Keeps all the data in one table. |
Extremely poor database design practice. Makes querying
the database for incidents that meet certain criteria extremely difficult
and time consuming. |
| Create Child Tables |
Best practice for database design. Allows for easily
adding new types of information. Does not create a "bulky" core
table. More efficient for querying the database for incidents that meet
certain criteria. |
Spreads data out over multiple tables which means that
greater database expertise is needed to develop and work with the data. |
The recommendation is to create an idbIncidents Table to handle the information
that is discrete (that is can only have one value such as the date of the
incident) and create a series of Child tables that store information where
there may be multiple pieces of data. This makes the database much more extendable.
If new information needs to be collected, either a new field would be added
to the idbIncidents Table or a new Child table could be created.
Database Structure 1.0
Since we are starting from almost scratch we can start with 1.0 as our
nomenclature. Below is a first iteration of the types of tables to be used in
a relational database for this project. This is based on the Wilderness Risk
Management Committee Incident Reporting database. The diagram shows the tables
and the various Parent/Child relationship. For those interested in the details of the Database Tables see Database Design Structure.
Database Naming Conventions & Tables
Table Names: Since the database could be incorporated into other existing databases (for example if desktop versions our developed) it is important to have a table naming scheme that is unique so that table names don't conflict with other existing table names (ex. using a generic table name like Category is bound to create conflicts). We have set up a table prefix of idb as the unique table prefix for all tables. For example, the central Incident table would be named idbIncidents.
Field Names: In order to allow for maximum readability and portability to other databases we have established a standard naming practice often used in database development. The field names can be multiple words where any spaces between words are separated with an underscore. A new word always begins with a capital letter. For example, the field name to record the date the incident occurred would be IncidentDate.
Primary Tables
Incident Table
The Incident Table is the core parent table of the database. Everything else relates to that as the primary Parent Table.
idbIncidents
| Field Name |
Data Type |
Purpose |
| IncidentID |
Autonumber |
Primary Key |
| SecureID |
Integer |
Foreign key relates to separate Organization table |
| ProgramTypeID |
Integer |
Type of Program - Lookup Table - idbProgramType |
| Age |
Integer |
Age of person involved in incident |
| Gender |
Text |
Gender of person involved in incident |
| StatusID |
Integer |
Status of person involved in incident - Lookup table - idbStatusType |
| StartDate |
DateTime |
Start date of program |
| IncidentTitle |
Text |
Descriptive Title of Incident |
| IncidentDate |
DateTime |
Date of Incident |
| IncidentTime |
DateTime |
Time of Incident |
| TotalDays |
Integer |
Total days of program (0 if less than 1 day) |
| ActiveHours |
Integer |
Total active program hours |
| DayOccurred |
Integer |
Day in the program incident occurred (ex. day 3 of 5 day
program) |
| IncidentType |
Integer |
Type of Incident - Loolup Table idbincidentType |
| LostDay |
Yes/No |
Did the incident cause the person to lose and hours/days
of program |
| DaysLost |
Integer |
Number of days lost |
| LeaveField |
Yes/No |
Did the person leave the program |
| LeaveDate |
DateTime |
Date the person left the program |
| EvacuationMethod |
Integer |
What method of evacuation was used - Lookup table - idbEvacuationMethod |
| MedicalVisit |
Yes/No |
Did the person receive medical care |
| VisitType |
Integer |
What type of medical care |
| ReturnField |
Yes/No |
Did the person return to the field (if the person left in
LeaveField) |
| ReturnDate |
DateTime |
Date the person returned |
| PropertyDamage |
Yes/No |
Was there property damage |
| DamageType |
Integer |
Type of property damage - Lookup Table idbDamageType |
| InjuryType |
Integer |
Type of Injury - Lookup Table - idbInjuryType |
| AnatomicalLocation |
Integer |
Anatomical location of Injury - Lookup Table - idbInjuryLocation |
| IllnessType |
Integer |
Type of Illness - Lookup Table - idbIllnessType |
| ActivityType |
Integer |
Primary sctivity person involved with at time of incident
- Lookup Table - idbActivityType |
| Narrative |
Text |
Description of Incident |
| Analysis |
Text |
Analysis of Incident by Reviewer |
| SubmitDate |
DateTime |
Date Incident Submitted |
| Preparer |
Text |
Prepared by |
| Preparer Position |
Text |
Position of Preparer |
| Submitdate |
DateTime |
Date incident submitted |
| Reviewer |
Text |
Reviewer |
| ReviewerPosition |
Text |
Position of Reviewer |
| ReviewerComment |
Text |
Comments by Reviewer |
| ReviewDate |
DateTime |
Date Reviewed |
| DateUpdated |
DateTime |
Date Updated |
This table is used to store data about the actual location of the incident.
Some programs may not want to store this data while others may want to store
it but do not want this information to be readily accessible to others. By
breaking it into a separate table that links back to the idbIncident table
in a One-to-One relationship with the value in the IncidentID field it creates
a level of security for this data. One can grant access to the overall idbIncident
Table but not allow access to the idbLocations table.
idbLocations Table
| Field Name |
Data Type |
Purpose |
| IncidentID |
Autonumber |
Primary key - Same ID as IncidentID in Incident table |
| Country |
Text |
Country |
| State |
Text |
State or Province |
| Area |
Text |
Area |
| Location |
Text |
Specific Location |
Child Tables - The Descriptions in this section are still under development
These tables all link to the Parent idbIncident Table. These are all tables
that enable multiple values in each table. For example, an incident might have
multiple contributing factors. These are stored in the idbContributingFactor
table and link back to the idbIncident Table using the IncidentID field value.
idbContributingFactor Table
Contributing factors to incident.
| Field Name |
Data Type |
Purpose |
| ContributingFactorID |
Autonumber |
Primary key |
| ContributingFactor |
Text |
Contributing factor(s) to incident |
| Active |
Yes/No |
Signifies if row is currently active |
idbEnvironmentType Table
Type of environment incident took place in.
| Field Name |
Data Type |
Purpose |
| EnvironmentTypeID |
Autonumber |
Primary key |
| EnvironmentType |
Text |
Type of environment incident took place in |
| Active |
Yes/No |
Signifies if row is currently active |
idbIllnessType Table
Type of illness.
| Field Name |
Data Type |
Purpose |
| IllnessTypeID |
Autonumber |
Primary key |
| Illnesstype |
Text |
Type of illness |
| Active |
Yes/No |
Signifies if row is currently active |
idbIncidentCondition Table
Lookup Tables
These a tables exist to populate drop down lists and menu lists on the Web submission form. This makes sure that data entry is consistent across submissions by restricting the selections.
idbActivityType Table
Type of primary activity when incident occurred. Feeds the ActivityTypeID
field in the idbIncidents Table.
| Field Name |
Data Type |
Purpose |
| ActivityTypeID |
Autonumber |
Primary Key |
| ActivityType |
Text |
Activity when accident took place |
| Active |
Yes/No |
Signifies if row is currently active - only those items where active = yes will show up on Web drop down lists. |
idbDamageType Table
Type of damage to property. Feeds the DamageTypeID field in the idbIncidents
Table.
| Field Name |
Data Type |
Purpose |
| DamageTypeID |
Autonumber |
Primary key |
| DamageType |
Text |
Type of property damage |
| Active |
Yes/No |
Signifies if row is currently active |
idbIncidentType
Incident Type. Feeds the IncidentTypeID field in the idbIncidents Table.
| Field Name |
Data Type |
Purpose |
| IncidentTypeID |
Autonumber |
Primary key |
| IncidentType |
Text |
Type of incident |
| Active |
Yes/No |
Signifies if row is currently active |
idbAnatomicalLocation Table
| Field Name |
Data Type |
Purpose |
| AnatomyID |
Autonumber |
Primary key |
| AnatomicalLocation |
Text |
Location of Injury |
| Active |
Yes/No |
Signifies if row is currently active |
Changes
10/2004
- Collapsed InjuryLocation table into a field in IncidentInjury table so
that injury location always maps to the specific injury
- Changed Priority field to Importance in IncidentFactor table