Bike and Walk Project

Data Dictionary

Entity Relationship Diagram

erd 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.