Database

= ACT-Data-Model =

About

 * Audience
 * This Data Model is in draft form and will be verified against objectives of its intended users in the Animal Crisis Team. If you are familiar with animal rescue, animal transport, or animal disaster relief operations and you would like to make suggestions, send email to me at 


 * E-R Model
 * These Data Model definitions are based on Entity-Relationships and is intended to document all relevant entities, relationships, and attributes. Each entity defines a table.  Each attribute becomes a table column.  Relationships between tables are established a) with associative tables for many-to-many links between table records or b) with foreign keys linking one record in one table to one of many records in another table.
 * Notation : table names have lowercase titles; key names have uppercase titles; (PK) indicates primary key; and (FK) indicated foreign key. You may click on table and key links to move between titles. The # symbol is not part of the table or key names.


 * Design Method
 * The definitions below will be entered into an E-R design tool to produce a graphic presentation for user reviews. The model is tested by asking questions expected in real world cases and then walking thru the tables to see if the expected answered can be provided. Business rules are enforced with constraints, cardinality, and data types. Once the Data Model becomes stable after indepth business analysis, it will be used to design and build a functional database to support web applications for ACT.

--jwalling 04:28, 5 November 2006 (UTC)

AIN

 * Animal ID Number
 * see table

animal

 * Animal Table


 * Animal ID
 * Animal ID Number _______________________________ (PK)
 * Animal ID Name _________________________________
 * Animal ID Location _____________________________
 * Animal ID Gender? Male, Female, Unknown
 * Animal ID Maturity _____________________________ (FK) (see note 4)
 * Animal ID Age (estimate) _______________________
 * Animal ID Dominant Breed _______________________
 * Animal ID Exact Breed? Yes, No, Unknown
 * Animal ID Primary Color ________________________
 * Animal ID Secondary Color ______________________
 * Animal ID Altered (S/N)? Yes, No, Unknown (see note 1)
 * Animal ID Declawed? Yes, No, Unknown
 * Animal ID Size? Small, Medium, Large, Extra-large, Unknown
 * Animal ID Weight in Pounds (est) _______________ (integer)
 * Animal ID Pet Finder ID ________________________
 * Animal ID Photo web page http://________________
 * Animal ID Comment ______________________________


 * Animal Health
 * Animal Health Shots up to date? Yes, No, Unknown
 * Animal Health Rabies Vaccine & Tag? Yes, No, Unknown (see note 2)
 * Animal Health Quarantined? No, Yes (7 days or more), Unknown
 * Animal Health Shots Needed (if any) ____________
 * Animal Health Certificate? Yes, No, Unknown (see note 3)
 * Animal Health External parasites: Fleas, Ticks, Other
 * Animal Health Internal parasites: Heartworm, Coccidia, Hookworm, Other
 * Animal Health On Medication ____________________
 * Animal Health Flea Control _____________________
 * Animal Health Tick Control _____________________
 * Animal Health Heartworm preventative ___________
 * Animal Health Wormed ___________________________
 * Animal Health Special Needs ____________________
 * Animal Health General __________________________
 * Animal Health Comment __________________________


 * Animal Behavior
 * Animal Behavior Tempermant? Aggressive, Assertive, Responsive, Shy, Unknown
 * Animal Behavior OK With Dogs? Yes, No, Unknown
 * Animal Behavior OK With Cats? Yes, No, Unknown
 * Animal Behavior OK With Kids? Yes, No, Unknown
 * Animal Behavior OK With Women? Yes, No, Unknown
 * Animal Behavior OK With Men? Yes, No, Unknown
 * Animal Behavior Housebroken? Yes, No, Unknown
 * Animal Behavior Crate Trained? Yes, No, Unknown
 * Animal Behavior OK In Cars? Yes, No, Unknown
 * Animal Behavior Problems _______________________
 * Animal Behavior Comment ________________________


 * Animal Associative Tables
 * 
 * 
 * 


 * Notes
 * 1 Altered: Must be altered if going to a forever home /private

party
 * 2 Rabies Vaccine: Rabies vaccines are legally required for entry into

and travel through most states. Check state regs for earliest age that vaccine is required. Most states require this at either 3 or 4 months of age.
 * 3 Health Certificates are legally required for entry

into/transport through most states.
 * 4 Foreign Key: >

animal-organization

 * - Associative Table
 * Animal ID Number _____________________ (PK)
 * Organization ID Number _______________ (PK)
 * Facility ID Number ___________________ (FK)
 * Category ID Number ___________________ (FK) (see note 1)
 * Update _______________________________
 * Comment ______________________________


 * Notes
 * 1 Foreign Key >

animal-person

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Animal ID Number _____________________ (PK)
 * Person ID Number _____________________ (PK)
 * Relationship? Adoption, Foster, Other
 * Date _________________________________
 * Comment ______________________________

category

 * Category Table
 * Category ID Number ___________________
 * Category Name ________________________
 * Category Description _________________
 * Category Animal-Organization? Yes, No, No Null
 * Category Animal? Yes, No, No NULL
 * Category Certificate? Yes, No, No NULL
 * Category Facility? Yes, No, No NULL
 * Category Item? Yes, No, No NULL
 * Category Maturity? Yes, No, No NULL
 * Category Person-Organization? Yes, No, No NULL
 * Category Run-Leg? Yes, No, No NULL

Animal-Organization Categories
Foreign Key: Adoption, New-Unassigned, Rescue, Shelter, Sponsor, Transport, Other

Animal Categories
Foreign Key: Barnyard, Bird, Cat, Dog, Horse, New-Unassigned, Other, Pig, Rabbit, Reptile, Small&Furry,

Certificate Categories
Foreign Key: Disaster, First Aid, Incident Command, Rescue, Transport,

Facility Categories
Primary Key: Agricultural-Center, Airport, Animal-Clinic, Animal-Evacuation, Animal-Shelter, Bird-Evacuation, Boarding-Kennel, Campground, Cat-Evacuation, Dog-Evacuation, Evacuation, Fairgrounds, Feed-Store, Foster-Care-Network, Garage, Grooming-Facility, Horse-Boarding, Hotel, House, Large-Animal-Evacuation, Lodging, Meal-Service, Motel, New-Unassigned, Other, People-Clinic, People-Evacuation, People-Hospital, People-Shelter, Pet-Day-Care-Center, Pet-Supply-Distribution, Pet-Supply-Store, Race-Track, Reptile-Evacuation, Rescue, Restaurant, Riding-Stables, Rodeo-Grounds, RV-Park, Small-Animal-Evacation, Storage, Transfer-Point, Transport-Service, Veterinary-Clinic, Veterinary-School, Warehouse, Waste-Disposal,

Item Categories
Primary Key: Air-Quality, Animal-Shelter, Apparel-Animal, Apparel-People, Appliance, Barrier, Bedding-Animal, Bedding-People, Book, Cage, Camera, Camping-Equipment, Capture, Carrier, Cleaning, Collar, Communication, Computer, Construction, Container, Cooling, Cover, Education, Electric, Entertainment, Finance, First-Aid, Food-Animal, Food-People, Fuel, Furniture, Gift-Card, Groom-Animal, Heat, Household, Housing, Hygiene-Animal, Hygiene-People, ID-Animal, ID-People, Kitchen-Utensil, Landscape, Light, Meals, Medical-Equipment, Medical-Service, Medicine-Animal, Medicine-People, New-Unassigned, Other, Power-Supply, Repellant-Animal, Repellant-Pest, Restraint-Animal, Safety, Sanitation, Security, Shelter-Animal, Shelter-People, Soap, Storage, Supply-Office, Telephone, Tie-Down, Toiletry, Toy-Animal, Transportation, Trap-Animal, Treat-Animal, Water,

Maturity Categories
Foreign Key: Adult, Baby, New-Unassigned, Senior, Unknown, Young,

Organization Categories
Primary Key: Agency-Government, Agency-Non-Government, New-Unassigned, Non-Profit, Shipping, Vendor,

Person-Oranization Categories
Foreign Key: Affiliate, Client, Customer, Director, Member, New-Unassigned, Officer, Other,

Run-Leg Categories
Foreign key New-Unassigned, Other, Overnight Filled, Overnight Needed Transport Filled, Transport Needed,

CEID

 * Certificate ID
 * natural key unique
 * see table

certificate

 * Certificate Table
 * Certificate ID _______________________ (PK) (natural key)
 * Certificate Name _____________________
 * Certificate Description ______________
 * Certificate Organization _____________ (FK)
 * Certificate Category _________________ (FK) (see note 1)
 * Required? Yes, No, NULL
 * Certificate Date Issued ______________
 * Certificate Date Expired _____________
 * Certificate Comment __________________


 * Notes
 * 1 Foreign Key >

CID

 * Country ID
 * natural key unique
 * see table

CIN

 * Category ID Number
 * see table

country

 * Country Table
 * Country ID ________________  (PK) natural key
 * Country name ______________
 * Country Abbreviation ______

facility

 * Facility Table
 * Facility ID Number ___________________ (PK)
 * Facility Name ________________________
 * Location ID Number ___________________ (FK)
 * Organization ID Number _______________ (FK)
 * Person ID Number _____________________ (FK)
 * Facility Phone _______________________
 * Facility Fax _________________________
 * Facility Email _______________________
 * Facility Number of Staff _____________
 * Facility Indoor Space ________________
 * Facility Outdoor Space _______________
 * Facility Number of Animals ___________
 * Facility Refrigeration? Yes, No, Unknown
 * Facility Climate control? Yes, No, Unknown
 * Facility Loading Dock? Yes, No, Unknown
 * Facility Security ____________________
 * Facility Cleaning area _______________
 * Facility Open Hours __________________
 * Facility Service Hours _______________
 * Facility Status? Open, Closed, Standby, Unknown
 * Facility Directions __________________
 * Facility Updtate _____________________


 * Associative tables
 * 

FIN

 * Facility ID Number
 * see table

facility

 * Facility Table
 * Facility ID Number ___________________ (PK)
 * Facility Name ________________________
 * Location ID Number ___________________ (FK)
 * Organization ID Number _______________ (FK)
 * Person ID Number _____________________ (FK)
 * Facility Phone _______________________
 * Facility Fax _________________________
 * Facility Email _______________________
 * Number of Staff ______________________
 * Indoor Space _________________________
 * Outdoor Space ________________________
 * Number of Animals ____________________
 * Animal Shelter? Yes, No, Unknown
 * Housing? Yes, No, Unknown
 * Meals? Yes, No, Unknown
 * Warehouse? Yes, No, Unknown
 * Refrigeration? Yes, No, Unknown
 * Climate control? Yes, No, Unknown
 * Loading Dock? Yes, No, Unknown
 * Security _____________________________
 * Cleaning area ________________________
 * Open Hours ___________________________
 * Service Hours ________________________
 * Status? Open, Closed, Standby, Unknown
 * Directions ___________________________
 * Updtate ______________________________

facility-category

 * - Associative Table
 * Cartinality many-to-many (M:M)


 * Facility ID Number ___________________ (PK)
 * Category ID Number ___________________ (PK) (see note 1)
 * Facility-Category Description ________


 * Notes
 * 1 Primary Key >

ICID

 * Incident Code ID
 * see table

incident

 * Incident Table
 * Incident ID Number ___________________ (PK)
 * Incident Code ________________________ (FK)
 * Incident Location ID Number __________ (FK)
 * Incident Description _________________

incident-code

 * Incident code table
 * Incident code ID ________________ (PK)  (natural key)
 * Incident code description _______


 * Examples
 * Yellow - monitoring conditions, anticipating a relief response
 * Orange - gathering resources for a relief response
 * Red - sending relief and/or relief is on site
 * Blue - winding down relief response
 * Green - relief response no longer needed

ININ

 * Incident ID Number
 * see table

item

 * Item Table
 * Item ID Number _______________________ (PK)
 * Item Name ____________________________
 * Item Description _____________________
 * Item Manufacturer Code _______________
 * Item Measurement _____________________
 * Item Quantity Per Pallet _____________
 * Item Perishable? Yes, No, Unknown
 * Item First Response? Yes, No, Unknown


 * Associative Tables
 * 
 * 

item-category

 * - Associative Table
 * Item ID Number _______________________ (PK)
 * Catgory ID Number ____________________  (PK) (see note 1)
 * Update _______________________________
 * Comment ______________________________


 * Notes
 * 1 Primary Key >

item-facility

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Item ID Number _______________________ (PK)
 * Facility ID Number ___________________  (PK)
 * Quantity Needed ______________________
 * Quantity Onhand ______________________
 * Urgent? Yes, No, Unknown
 * Update _______________________________
 * Comment ______________________________

ITIN

 * Item ID Number
 * see table

LEGID

 * Leg ID
 * natural key unique to run
 * see table

LIN

 * Location ID Number
 * Table

location

 * Location Table
 * Location ID Number ___________________ (PK)
 * Freeform Address _____________________
 * Street1 ______________________________
 * Street2 ______________________________
 * City _________________________________
 * State ID _____________________________ (FK)
 * State County ID Number _______________ (FK)
 * Zipcode ______________________________
 * Region ______________________________
 * Neighborhood _________________________
 * Map ID _______________________________ (FK)

map

 * Map Table
 * Map ID Number ________________________ (PK)
 * Map URL: http://______________________ URL
 * Map name _____________________________
 * Map description ______________________
 * Map server ___________________________

map-marker

 * Map Marker Table
 * Map ID Number ________________________ (PK1)
 * Map Marker ID ________________________ (PK2) (text address)
 * Map Marker ID Number _________________ (external FK) (opt**)
 * Optional MMIN required for map updates
 * Map Marker Title _____________________
 * Map Marker Directions? TRUE, FALSE
 * Map Marker Delete? FALSE, TRUE
 * Website ______________________________ (optional)
 * Order ________________________________ (1-9999)
 * Latitude _____________________________ (datatype: decimal)
 * Longitude ____________________________ (datatype: decimal)
 * Category ID Number ___________________

MIN

 * Map ID Number
 * see table

OIN

 * Organization ID Number
 * see table

organization

 * Organization Table
 * Organization ID Number _______________ (PK)
 * Organization Name ____________________
 * Location ID Number ___________________ (FK)
 * Organization Phone ___________________
 * Organization Email ___________________
 * Organization Contact Person __________ (FK)
 * Organization Web page ________________
 * Organization Pet finder web page _____ (see note 1)
 * Organization Supply Contact Person ___ (FK)
 * Organization Comment ______________________________


 * Organization Associative Tables
 * 
 * 
 * 


 * Notes
 * 1 Pet Finder http://www.petfinder.org/shelters/AA###.html

organization-category

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Organization ID Number _______________ (PK)
 * Category ID Number ___________________ (PK) (see note 1)
 * Comment ______________________________


 * Notes
 * 1 Primary Key >

organization-organization

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Organization ID Number _______________ (PK)
 * Organization ID Number _______________ (PK)
 * Relationship: ________________________
 * Date _________________________________
 * Comment ______________________________

person

 * Person Table


 * Person ID
 * Person ID Number _____________________ (PK)
 * Name ________________________________
 * Location ID Number ___________________ (FK)
 * Home phone __________________________
 * Cell phone ___________________________
 * Work phone ___________________________
 * Skype ________________________________
 * Pager ________________________________
 * Email _______________________________
 * Email2 ______________________________
 * AOL Instant Messenger (AIM) __________
 * Windows Live Messenger(MSN) __________
 * Yahoo! Messenger (Y!M) _______________
 * Google Talk __________________________
 * Organization _________________________ (OIN) (FK)
 * Web page http://______________________
 * Comment ______________________________


 * Person Volunteer (Person Subtype)
 * Person ID Number _____________________ (PK)
 * Volunteer Availability _______________
 * Volunteer Skills _____________________
 * Volunteer Categories: Transport, Supply, Foster, Operations, Fundraising, Technology, Other
 * Volunteer Vehicle ____________________


 * Person Transport (Person Subtype)
 * Person ID Number _____________________ (PK)
 * Transport states _____________________
 * Transport areas ______________________
 * Transport distances __________________
 * Days available: Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday
 * Animal sizes _________________________
 * Animal types _________________________
 * Vehicle description __________________
 * License plate# ______________________
 * Emergency contact w phone ___________
 * Preferred transfer points ___________
 * Restrictions _________________________
 * Comment _____________________________


 * Person Associative Tables
 * 
 * 
 * 

person-organization

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Person ID Number _____________________ (PK)
 * Organization ID Number _______________ (PK)
 * Category ID Number ___________________ (FK)
 * Date _________________________________
 * Comment __________________________

PIN

 * Person ID Number
 * see table

REGIN

 * Region ID Number
 * see table

RIN

 * Run ID Number
 * see table

region

 * Region Table
 * Region ID Number _____________________ (PK)
 * Region Name __________________________
 * Region Description ___________________


 * Region Name examples:
 * AL/North Central
 * FL/Keys
 * FL/Panhandle
 * LA/New Orleans area
 * US/Gulfcoast TX LA MS
 * US/Nation wide
 * US/New England
 * US/Northwest
 * WA/Olympic Peninsula

run

 * Run Table
 * aka RUN SHEET


 * Run ID Number ____________________________ (PK)
 * Run name _________________________________
 * Run Description __________________________
 * Run From City/State ______________________
 * Run To City/State ________________________
 * Run Start Date ___________________________
 * Run End Date _____________________________
 * Run Coordinator __________________________ (FK)
 * Run Sponsor Organization _________________ (FK)
 * Run Sponsor Contact Person _______________ (FK)
 * Run Sending Organization _________________ (FK)
 * Run Sending Contact Person _______________ (FK)
 * Run Receiving Organization _______________ (FK)
 * Run Receiving Contact Person _____________ (FK)
 * Run Transport From _______________________ (see note 2)
 * Run Transport To _________________________ (see note 3)
 * Run Crates Provided ______________________
 * Run Crates Sizes _________________________
 * Run Crates Provider ______________________ (FK)
 * Run Crates Comment _______________________ (see note 1)
 * Run Additional Items _____________________
 * Run Web Page http://______________________
 * Run Map Web Page http://__________________
 * Run Comment ______________________________


 * Run Associative Tables
 * 
 * 


 * Notes
 * 1 Run Crates Comment - If crate(s) is/are not provided, indicate how animals will be safely carried
 * 2 Examples: Shelter, Rescue, Other
 * 3 Examples: Rescue, Adoption site, Approved home, Other

run-animal

 * - Associative Table
 * Cardinality: zero-to-many (0:M)


 * Run ID Number __________________________________ (PK)
 * Animal ID Number _______________________________ (PK)
 * Run-animal Crate Required? Yes, No, Unknown
 * Run-animal Crate Provided? Yes, No, Unknown
 * Run-animal Crate Size ___________________________
 * Run-animal Crate Companion ______________________
 * Run-animal Items ________________________________ (see note 1)
 * Run-animal Comment ______________________________


 * Notes
 * 1 Examples: Collar, Leash, Harness, Medicine, Vet paperwork, Health certificate

run-leg

 * -Leg Table
 * Cardinality: zero-to-many (0:M)


 * Run ID Number ________________________ (PK)
 * Leg ID _______________________________ (PK) (Natural key)
 * Leg Location ID Number _______________ (FK)
 * Leg Transport Person _________________ (FK)
 * Category ID Number ___________________ (FK) (see note 1)
 * Leg Description ______________________
 * Leg From City ________________________
 * Leg From State _______________________ ((FK)
 * Leg To City __________________________
 * Leg To State _________________________ (FK)
 * Leg Date _____________________________
 * Leg Estimated Start Time _____________
 * Leg Estimated Time ___________________
 * Leg Distance _________________________
 * Leg Directions _______________________
 * Leg Transporter ______________________ (FK)
 * Leg Comment __________________________


 * Notes
 * 1 Foreign Key: >

SCIN

 * State-county ID Number
 * see table

state

 * State and Province Table
 * State ID _________________ (PK) natural key
 * State name _______________
 * State region _____________
 * State country ____________

state-county
County, Parrish, and Province Table


 * State County
 * State ID _______________________________ (PK)
 * State County ID Number _________________ (PK)
 * County Name ____________________________

STID

 * State ID
 * natural key
 * see table

item-facility

 * - Associative Table
 * Cardinality: many-to-many (M:M)


 * Item ID Number _______________________ (PK)
 * Facility ID Number ___________________  (PK)
 * Quantity Needed ______________________
 * Quantity Onhand ______________________
 * Urgent? Yes, No, Unknown
 * Update _______________________________
 * Comment ______________________________

= See also =

Internal links

 * Database Terms
 * Technology
 * TechnologyWebDesign

CSV conversion online

 * Determine file type
 * http://www.iconv.com/file.htm
 * File extension list
 * http://www.iconv.com/fileextensions.htm
 * CSV to Wiki or HTML markup here
 * http://area23.brightbyte.de/csv2wp.php
 * CSV to Tab delimited
 * http://www.iconv.com/csv2delimited.htm
 * CSV to XML
 * http://www.creativyst.com/cgi-bin/Prod/15/eg/csv2xml.pl
 * CSV to Javascript
 * http://www.creativyst.com/Prod/17/
 * Excel XLS to CSV
 * http://www.iconv.com/xls2csv.htm
 * Yahoo calendar CSV to iCal
 * http://www.manastungare.com/projects/yahoo2ical/