Data Dictionary
Entity Relationship Diagram

Table Definitions
Updated April 4, 2018
Primary keys have been omitted for simplicity here. A numeric key field named "ID" included in each table.
organization |
As I anticipate the database being used as a central repository to be used by various organizations, it would be useful to have a way to view counts, settings and data that are created by a given organization |
name |
The name of the Organization |
email |
contact email for the org. administrator |
defaultTimeZone |
The time zone to be used by default when creating countEvent records for this organization. A 3 char string such as "PST", "EST", etc. |
user |
This table contains details about the people who do the counting |
name |
|
email |
|
userName |
it is a user table after all... |
password |
|
role |
Used to determine the level of access this user has to the database. Defaults to "counter" but could be administrator |
inactive |
We don't want to delete a record after someone has participated in a count so we can set it to inactive to hide it during data input as a convenience to the administrator. Defaults to false. |
userOrganization |
An associative table that links a user to an organization |
user_ID |
Foreign key link to the user record. |
organization_ID |
Foreign key link to the organization record. |
location |
Represents a physical location where a count is to take place |
locationName |
A descriptive name for reference |
NS_Street |
The street that runs north and south at this location |
EW_Street |
The street that runs east and west at this location |
locationType |
Is this counting location at an "intersection" or is it "screenline" (mid-block)? |
city |
|
state |
|
latitude |
For mapping |
longitude |
For mapping |
organization_ID |
Foreign key link to Organization |
countEvent |
This table represents traffic count being conducted by one Organization on a particular date and time. The count will be conducted at one or more locations. |
title |
A descriptive title for this event. |
startDate |
The starting date and time |
endDate |
The ending date and time |
timeZone |
The time zone where the counting will take place. In the format "PST", "EST", etc. |
isDST |
0 if event is not during daylight savings time, else 1 |
weather |
A numerical code that describes the weather conditions at the time of the count. A non-null value here will also act as an indicator that the count is complete for this location. |
organization_ID |
Foreign key link to Organization |
assignment |
(formerly named "countingLocation") This table represents a single location where the count is being done as part of the related count event. |
assignmentUID |
A unique identifier string that will be used to load the Count Location data into the client app. The goal is to create a hard to guess string to make it unlikely that someone other than the assigned person could guess the uri and be able to intentionally or accidentally enter bad data into the database. |
countEvent_ID |
Foreign key link to the Count Event record |
location_ID |
Foreign key link to the location record. |
user_ID |
Foreign key link to the person assigned to count at this location |
invitationSent |
A text field that will be the empty string if no email invitation has been send
for this assignment. Otherwise it will contain some text. Probably a date string. |
trip |
This table represents the count data. |
tripCount |
The number of travelers |
tripDate |
Date and time of trip. |
seqNo |
A sequence number used in conjunction with the assignment record ID. It represents a trip by one or more travelers and is used to undo trips that the user
has entered in error. |
turnDirection |
A code that indicates the direction of travel upon entering the intersection and whether the traveler went straight through or turned, and if so, in what direction. Using the Bicycle and Pedestrian Documentation Project coding of A1, B2, etc. as used on their count sheet. |
location_ID |
Foreign key link to Count Location record |
traveler_ID |
Foreign key link to the Traveler record |
provisionalTrip |
There may be a situation where the server receives a new trip record request which could not be validated for
some reason. Rather that reject the record, the system will record the trip here with some description of
the issue.
An administrator will need to manually review the record for correction and transfer to the trip table.
This table is identical to the trip table with the addition of the field below.
|
... includes all fields form Trip table plus ... |
issue |
A message about the reason(s) the trip record didn't validate |
eventTraveler |
An associative table to indicate which Traveler types are to be counted during an Count Event. |
sortOrder |
used to control the order in which Traveler icons are displayed in the client app. |
countEvent_ID |
Foreign key link to the Count Event record |
traveler_ID |
Foreign key link to the Traveler record. |
traveler |
Represents the Traveler types that may be counted. |
name |
A name for reference. |
description |
A description of the intended purpose of this Traveler and it's associated Features |
iconURL |
Unambiguous internet location where an icon for this Traveler type can be found. |
travelerCode |
a unique text code assigned to this traveler. Use as a short cut to select for this traveler when reporting etc. |
travelerFeature |
An associative table that links a Traveler record to one or more Feature records. |
traveler_ID |
Foreign key link to the Traveler record. |
feature_ID |
Foreign key link to the Feature record. |
feature |
A feature may describe a wide range of details about a traveler that has been counted. The possible list may include:
|
featureClass |
A category that tends to classify the feature being described. Initially the classes may include:
- Mode (Bike, Walk, etc)
- Gender
- Behavior (Sidewalk Riding, Helmet use, etc.)
- Age Group
|
featureValue |
A value for the featureClass |
outbox |
A temporary storage table for email messages awaiting processing. After a message has been successfully
sent, the outbox record is deleted.
After some number of failed attempts, the admin is informed of the failure and the outbox record is deleted.
|
from |
From address to use for email. Usually the admin email address form the
organization for which the email is being sent
|
to |
Recipient email address or comma separated addresses. |
replyTo |
Reply-to email address if diff from "from". |
CC |
Carbon Copy recipient email address or comma separated addresses. |
BCC |
BCC recipient email address or comma separated addresses. |
subject |
Subject text |
messageText |
The text version of the email message.
|
messageHTML |
The HTML version of the email message if any.
|
sendAttemptCount |
The number of times the system attempted and failed to send the message. After some number of failed attempts
the sys admin will be notified and the outbox record will be deleted.
|
lastSendTime |
The DateTime of the last send attempt so we don't attempt to resend failed messages too fast.
|
dateSent |
The DateTime when the message was successfully sent. This is useful if the message is sent, but the record could
not be deleted for some reason so that we don't keep sending the message forever.
|
sendHistory |
Success or error responses from mail server.
|