FANDOM


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 <wallingconsulting @ yahoo .com>
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 #animal table


animal

Animal Table
Animal ID
  • Animal ID Number _______________________________ #AIN (PK)
  • Animal ID Name _________________________________
  • Animal ID Location _____________________________
  • Animal ID Gender? Male, Female, Unknown
  • Animal ID Maturity _____________________________ #CIN (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
#animal-organization
#animal-person
#run-animal


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: #category > #Maturity Categories

animal-organization

#animal-#organization Associative Table
  • Animal ID Number _____________________ #AIN (PK)
  • Organization ID Number _______________ #OIN (PK)
  • Facility ID Number ___________________ #FIN (FK)
  • Category ID Number ___________________ #CIN (FK) (see note 1)
  • Update _______________________________
  • Comment ______________________________
Notes
1 Foreign Key #category > #Animal-Organization Categories

animal-person

#animal-#person Associative Table
Cardinality: many-to-many (M:M)
  • Animal ID Number _____________________ #AIN (PK)
  • Person ID Number _____________________ #PIN (PK)
  • Relationship? Adoption, Foster, Other
  • Date _________________________________
  • Comment ______________________________


category

Category Table
  • Category ID Number ___________________ #CIN
  • 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: #animal-organization

Adoption, New-Unassigned, Rescue, Shelter, Sponsor, Transport, Other

Animal Categories

Foreign Key: #animal

Barnyard, Bird, Cat, Dog, Horse, New-Unassigned, Other, Pig, Rabbit, Reptile, Small&Furry,

Certificate Categories

Foreign Key: #certificate

Disaster, First Aid, Incident Command, Rescue, Transport,

Facility Categories

Primary Key: #facility-category

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: #item-category

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: #animal

Adult, Baby, New-Unassigned, Senior, Unknown, Young,

Organization Categories

Primary Key: #organization-category

Agency-Government, Agency-Non-Government, New-Unassigned, Non-Profit, Shipping, Vendor,

Person-Oranization Categories

Foreign Key: #person-organization

Affiliate, Client, Customer, Director, Member, New-Unassigned, Officer, Other,

Run-Leg Categories

Foreign key #run-leg

New-Unassigned, Other, Overnight Filled, Overnight Needed Transport Filled, Transport Needed,

CEID

Certificate ID
natural key unique
see #certificate table


certificate

Certificate Table
  • Certificate ID _______________________ #CEID (PK) (natural key)
  • Certificate Name _____________________
  • Certificate Description ______________
  • Certificate Organization _____________ #OIN (FK)
  • Certificate Category _________________ #CIN (FK) (see note 1)
  • Required? Yes, No, NULL
  • Certificate Date Issued ______________
  • Certificate Date Expired _____________
  • Certificate Comment __________________
Notes
1 Foreign Key #category > #Certificate Categories

CID

Country ID
natural key unique
see #country table

CIN

Category ID Number
see #category table

country

Country Table
  • Country ID ________________ #CID (PK) natural key
  • Country name ______________
  • Country Abbreviation ______

facility

Facility Table
  • Facility ID Number ___________________ #FIN (PK)
  • Facility Name ________________________
  • Location ID Number ___________________ #LIN (FK)
  • Organization ID Number _______________ #OIN (FK)
  • Person ID Number _____________________ #PIN (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
#facility-category



FIN

Facility ID Number
see #facility table

facility

Facility Table
  • Facility ID Number ___________________ #FIN (PK)
  • Facility Name ________________________
  • Location ID Number ___________________ #LIN (FK)
  • Organization ID Number _______________ #OIN (FK)
  • Person ID Number _____________________ #PIN (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

#facility-#category Associative Table
Cartinality many-to-many (M:M)
  • Facility ID Number ___________________ #FIN (PK)
  • Category ID Number ___________________ #CIN (PK) (see note 1)
  • Facility-Category Description ________


Notes
1 Primary Key #category > #Facility Categories


ICID

Incident Code ID
see #incident-code table

incident

Incident Table
  • Incident ID Number ___________________ #ININ (PK)
  • Incident Code ________________________ #ICID (FK)
  • Incident Location ID Number __________ #LIN (FK)
  • Incident Description _________________

incident-code

Incident code table
  • Incident code ID ________________ #ICID (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 #incident table


item

Item Table
  • Item ID Number _______________________ #ITIN (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
#item-facility

item-category

#item-#category Associative Table
  • Item ID Number _______________________ #ITIN (PK)
  • Catgory ID Number ____________________ #CIN (PK) (see note 1)
  • Update _______________________________
  • Comment ______________________________
Notes
1 Primary Key #category > #Item Categories


item-facility

#item-#facility Associative Table
Cardinality: many-to-many (M:M)
  • Item ID Number _______________________ #ITIN (PK)
  • Facility ID Number ___________________ #FIN (PK)
  • Quantity Needed ______________________
  • Quantity Onhand ______________________
  • Urgent? Yes, No, Unknown
  • Update _______________________________
  • Comment ______________________________

ITIN

Item ID Number
see #item table

LEGID

Leg ID
natural key unique to run
see #run-leg table

LIN

Location ID Number
#location Table


location

Location Table
  • Location ID Number ___________________ #LIN (PK)
  • Freeform Address _____________________
  • Street1 ______________________________
  • Street2 ______________________________
  • City _________________________________
  • State ID _____________________________ #STID (FK)
  • State County ID Number _______________ #SCIN (FK)
  • Zipcode ______________________________
  • Region ______________________________
  • Neighborhood _________________________
  • Map ID _______________________________ #MIN (FK)

map

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

map-marker

Map Marker Table
  • Map ID Number ________________________ #MIN (PK1)
  • Map Marker ID ________________________ #MMID (PK2) (text address)
  • Map Marker ID Number _________________ #MMIN(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 ___________________ #CIN

MIN

Map ID Number
see #map table

OIN

Organization ID Number
see #organization table

organization

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


Organization Associative Tables
#organization-animal
#organization-organization
#person-organization
Notes
1 Pet Finder http://www.petfinder.org/shelters/AA###.html

organization-category

#organization-#category Associative Table
Cardinality: many-to-many (M:M)
  • Organization ID Number _______________ #OIN (PK)
  • Category ID Number ___________________ #CIN (PK) (see note 1)
  • Comment ______________________________
Notes
1 Primary Key #category > #Organization Categories

organization-organization

#organization-#organization Associative Table
Cardinality: many-to-many (M:M)
  • Organization ID Number _______________ #OIN (PK)
  • Organization ID Number _______________ #OIN (PK)
  • Relationship: ________________________
  • Date _________________________________
  • Comment ______________________________

person

Person Table
Person ID
  • Person ID Number _____________________ #PIN (PK)
  • Name ________________________________
  • Location ID Number ___________________ #LIN (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 _____________________ #PIN (PK)
  • Volunteer Availability _______________
  • Volunteer Skills _____________________
  • Volunteer Categories: Transport, Supply, Foster, Operations, Fundraising, Technology, Other
  • Volunteer Vehicle ____________________
Person Transport (Person Subtype)
  • Person ID Number _____________________ #PIN (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
#animal-person
#person-organization

person-organization

#person-#organization Associative Table
Cardinality: many-to-many (M:M)
  • Person ID Number _____________________ #PIN (PK)
  • Organization ID Number _______________ #OIN (PK)
  • Category ID Number ___________________ #CIN (FK)
  • Date _________________________________
  • Comment __________________________

PIN

Person ID Number
see #person table

REGIN

Region ID Number
see #region table


RIN

Run ID Number
see #run table

region

Region Table
  • Region ID Number _____________________ #REGIN (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 ____________________________ #RIN (PK)
  • Run name _________________________________
  • Run Description __________________________
  • Run From City/State ______________________
  • Run To City/State ________________________
  • Run Start Date ___________________________
  • Run End Date _____________________________
  • Run Coordinator __________________________ #PIN (FK)
  • Run Sponsor Organization _________________ #OIN (FK)
  • Run Sponsor Contact Person _______________ #PIN (FK)
  • Run Sending Organization _________________ #OIN (FK)
  • Run Sending Contact Person _______________ #PIN (FK)
  • Run Receiving Organization _______________ #OIN (FK)
  • Run Receiving Contact Person _____________ #PIN (FK)
  • Run Transport From _______________________ (see note 2)
  • Run Transport To _________________________ (see note 3)
  • Run Crates Provided ______________________
  • Run Crates Sizes _________________________
  • Run Crates Provider ______________________ #PIN (FK)
  • Run Crates Comment _______________________ (see note 1)
  • Run Additional Items _____________________
  • Run Web Page http://______________________
  • Run Map Web Page http://__________________
  • Run Comment ______________________________
Run Associative Tables
#run-animal
#run-leg
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

#run-#animal Associative Table
Cardinality: zero-to-many (0:M)
  • Run ID Number __________________________________ #RIN (PK)
  • Animal ID Number _______________________________ #AIN (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

#run-Leg Table
Cardinality: zero-to-many (0:M)
  • Run ID Number ________________________ #RIN (PK)
  • Leg ID _______________________________ #LEGID (PK) (Natural key)
  • Leg Location ID Number _______________ #LOCID (FK)
  • Leg Transport Person _________________ #PIN (FK)
  • Category ID Number ___________________ #CIN (FK) (see note 1)
  • Leg Description ______________________
  • Leg From City ________________________
  • Leg From State _______________________ #STID ((FK)
  • Leg To City __________________________
  • Leg To State _________________________ #STID (FK)
  • Leg Date _____________________________
  • Leg Estimated Start Time _____________
  • Leg Estimated Time ___________________
  • Leg Distance _________________________
  • Leg Directions _______________________
  • Leg Transporter ______________________ #PIN (FK)
  • Leg Comment __________________________
Notes
1 Foreign Key: #category > #Run-Leg Categories

SCIN

State-county ID Number
see #state-county table


state

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

state-county

County, Parrish, and Province Table

State County
  • State ID _______________________________ #SID (PK)
  • State County ID Number _________________ #SCIN (PK)
  • County Name ____________________________

STID

State ID
natural key
see #state table


item-facility

#item-#facility Associative Table
Cardinality: many-to-many (M:M)
  • Item ID Number _______________________ #ITIN (PK)
  • Facility ID Number ___________________ #FIN (PK)
  • Quantity Needed ______________________
  • Quantity Onhand ______________________
  • Urgent? Yes, No, Unknown
  • Update _______________________________
  • Comment ______________________________




See also

Internal links

External links

Tools

CSV conversion online


Categories