Data Schema

Data Schema

The database can store these entities and the attributes associated with them. Required fields and other notes are also noted.

Participant

Relationships:
The database allows a Participant to have zero or more Child records The database allows a Participant to be associated with zero or more Programs

Attribute Name	Data Type	Required?	Validations	Notes
first_name	string (100 characters)	Y	-
last_name	string (100 characters)	Y	-
dob	date	Y	-
gender	pre-defined list (enum)	Y	-	may be: male, female, other
ethnicity	pre-defined list (enum)	N	-	may be: White, Hispanic, Black, Asian or Pacific Islander, Native American or Alaskan Native, Other
email	string (255 characters)	N	-
phone	string (255 characters)	N	-
county	string (255 characters)	N	-
line_1	string (255 characters)	N	-
line_2	string (255 characters)	N	-
city	string (255 characters)	N	-
state	string (255 characters)	N	-
zip	string (100 characters)	N	-
ok_to_text	yes/no	Y	-	default 'false'
last_grade_completed	pre-defined list (enum)	N	-	may be: 1st, 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, 11th, 12th
employment_status	pre-defined list (enum)	N	-	may be: employed, unemployed
number_of_children	integer	N	-
annual_income	decimal	N	-
additional_services	long text	N	-	other programs participant receives
referrer	long text	N	-	agency/individual who referred participant/contact info
status	pre-defined list (enum)	Y	-	may be: applicant (default), participant
enrollment_date	date	N	-
exit_date	date	N	-
service_interest	string (512 characters)	N	-	list of services applicant is interested in when they sign up on web form

Child

Relationships:
The database requires a child to have one associated Participant

Attribute Name	Data Type	Required?	Validations	Notes
participant_id	foreign key	Y	-	Ties Child record to a Participant record
first_name	string (100 characters)	Y	-
last_name	string (100 characters)	Y	-
dob	date	Y	-
gender	pre-defined list (enum)	Y	-	may be: male, female, other, unknown
email	string (255 characters)	N	-
phone	string (255 characters)	N	-
county	string (255 characters)	N	-
line_1	string (255 characters)	N	-
line_2	string (255 characters)	N	-
city	string (255 characters)	N	-
state	string (255 characters)	N	-
zip	string (100 characters)	N	-
care_info	string (255 characters)	N	-	Notes about child care, school, etc.
notes	long text	N	-	Multi-purpose notes about child
dad_involved	yes/no	Y	-	default 'false'
cps_involvement	yes/no	Y	-

Volunteer

Relationships:
The database allows a Volunteer to have zero or more Time Entry records

Attribute Name	Data Type	Required?	Validations	Notes
first_name	string (255 characters)	Y	-
last_name	string (255 characters)	Y	-
dob	date	Y	-
gender	pre-defined list (enum)	Y	-	may be: male, female, other, unknown (default)
email	string (255 characters)	Y	-
phone	string (255 characters)	Y	-
county	string (255 characters)	N	-
line_1	string (255 characters)	N	-
line_2	string (255 characters)	N	-
city	string (255 characters)	N	-
state	string (255 characters)	N	-
zip	string (255 characters)	N	-
start_date	date	N	-
active	yes/no	Y	-	default 'false'
background_check	yes/no	Y	-	default 'false'
ethnicity	pre-defined list (enum)	Y	-	may be: White, Hispanic, Black, Asian or Pacific Islander, Native American or Alaskan Native, Other (default)
ok_to_text	yes/no	Y	-	If volunteer would like to get promotional emails (default 'true')

Time Entry

Relationships:
The database requires a Time Entry to have one associated Volunteer

Attribute Name	Data Type	Required?	Validations	Notes
volunteer_id	foreign key	Y	-	Ties a Time Entry to a Volunteer
date	date	Y	-
duration_hours	decimal	Y	-
description	string (255 characters)	Y	-	Description of volunteer service

Program

Relationships:
The database allows Programs to be associated with zero or more Participants

Attribute Name	Data Type	Required?	Validations
name	string (255 characters)	Y	-

Participant-Program

Relationships:
This database entity tracks the many-to-many relationship between a Participants and Programs

Attribute Name	Data Type	Required?	Validations
participant_id	foreign key	Y	-
program_id	foreign key	Y	-

Donor

Relationships:
The database allows a Donor to have zero or more Donation records

Attribute Name	Data Type	Required?	Validations	Notes
first_name	string (255 characters)	Y	-
last_name	string (255 characters)	Y	-
email	string (255 characters)	Y	-
phone	string (255 characters)	Y	-
county	string (255 characters)	N	-
line_1	string (255 characters)	N	-
line_2	string (255 characters)	N	-
city	string (255 characters)	N	-
state	string (255 characters)	N	-
zip	string (255 characters)	N	-

Donation

Relationships:
The database requires a Donation to have one associated Donor

Attribute Name	Data Type	Required?	Validations	Notes
donation_type	pre-defined list (enum)	Y	-	may be: one_time, recurring
in_kind	yes/no	Y	-	default 'false'
amount	decimal	Y	-