Appendix: Database Tables/Columns
=================================

adoption
--------

The adoption table holds a row for each movement attached to an animal or person. 

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
AdoptionNumber 
    VARCHAR 
    A unique string identifying this movement (defaults to adoption.ID) 
AnimalID 
    INTEGER 
    Link to the animal table 
OwnerID 
    INTEGER 
    Link to the owner table 
RetailerID 
    INTEGER 
    Link to the owner table for a retailer (0 for no retailer) 
OriginalRetailerMovementID 
    INTEGER 
    Link to another movement in this table for an original movement to retailer that started this adoption 
MovementDate 
    TIMESTAMP 
    The date the animal moved 
MovementType 
    INTEGER 
    Link to the lksmovementtype table for the type of animal movement (none = reservation, adoption, foster, transfer, escaped, stolen, reclaimed, etc)
ReturnDate 
    TIMESTAMP 
    The date the animal came back to the shelter from this movement (or null for not returned) 
ReturnedReasonID 
    INTEGER 
    Link to the entryreason table for reason for return 
InsuranceNumber 
    VARCHAR 
    If your shelter does short term insurance when adopting, the policy number 
ReasonForReturn 
    VARCHAR 
    Free text, the reason the animal was returned 
ReservationDate 
    TIMESTAMP 
    For reservation movements, the reservation date 
Donation 
    FLOAT 
    A total of all donations attached to this movement 
ReservationCancelledDate 
    TIMESTAMP 
    If this is a reservation and it has been cancelled, the date it was cancelled 
IsTrial
    INTEGER
    1 if this is a trial adoption movement 
TrialEndDate
    TIMESTAMP
    The date this trial ends if it is a trial adoption movement 
Comments 
    VARCHAR 
    Movement comments 
RecordVersion 
    INTEGER 
    Optimistic lock flag 
CreatedBy 
    VARCHAR 
    User who created this record 
CreatedDate 
    TIMESTAMP 
    Date this record was created 
LastChangedBy 
    VARCHAR 
    User who last changed this record 
LastChangedDate 
    TIMESTAMP 
    Date this record was last changed 

animal
------

The animal table holds a row for each animal on the system. Various denormalised fields at the end are used to track the current movement, whether the animal is on shelter, etc. These are useful for writing reports. 

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
AnimalTypeID 
    INTEGER 
    Link to the animaltype table 
AnimalName 
    VARCHAR 
    The animal's name 
NonShelterAnimal 
    INTEGER 
    1 if the animal is a non-shelter animal (kept out of figures) 
CrueltyCase 
    INTEGER 
    1 if the animal is a cruelty case (all 0/1 fields link to lksyesno table) 
BondedAnimalID 
    INTEGER 
    Animal ID of bonded animal 1 (or 0) 
BondedAnimal2ID 
    INTEGER 
    Animal ID of bonded animal 2 (or 0) 
BaseColourID 
    INTEGER 
    Link to the basecolour table 
SpeciesID 
    INTEGER 
    Link to the species table 
BreedID 
    INTEGER 
    Primary breed (link to the breed table) 
Breed2ID 
    INTEGER 
    Secondary breed (same as primary if not crossbreed) 
BreedName 
    VARCHAR 
    Name of breed in form breed1 / breed2 if crossbreed 
CrossBreed 
    INTEGER 
    1 if animal is a crossbreed 
CoatType 
    INTEGER 
    Link to lkcoattype table 
Markings 
    VARCHAR 
    Distinguishing features field 
ShelterCode 
    VARCHAR 
    The animal's shelter code 
ShortCode 
    VARCHAR 
    The short version of the shelter code 
UniqueCodeID 
    INTEGER 
    The next UUU code can be generated by doing MAX(UniqueCodeID) 
YearCodeID 
    INTEGER 
    The next NNN code is MAX(YearCodeID) for matching brought in year and type 
AcceptanceNumber 
    VARCHAR 
    The acceptance number/Litter ID 
DateOfBirth 
    TIMESTAMP 
    The animal's date of birth 
AgeGroup 
    VARCHAR 
    The animal's age group based on date of birth at the most recent time it entered the shelter
AgeGroupActiveMovement
    VARCHAR
    The animal's age group based on date of birth at the last time it left the shelter
DeceasedDate 
    TIMESTAMP 
    Date the animal died, if null the animal is still alive 
Sex 
    INTEGER 
    Link to the lksex table 
Identichipped 
    INTEGER 
    1 if the animal is microchipped 
IdentichipNumber 
    VARCHAR 
    The animal's microchip number 
IdentichipDate 
    TIMESTAMP 
    The date the animal was microchipped 
Tattoo 
    INTEGER 
    1 if the animal has an ear tattoo 
TattooNumber 
    VARCHAR 
    The animal's tattoo number 
TattooDate 
    TIMESTAMP 
    The date the animal was tattooed 
Neutered 
    INTEGER 
    1 if the animal has been neutered/spayed 
NeuteredDate 
    TIMESTAMP 
    The date the animal was neutered/spayed 
Declawed 
    INTEGER 
    1 if the animal has been declawed 
HiddenAnimalDetails 
    VARCHAR 
    Hidden animal comments 
AnimalComments 
    VARCHAR 
    The animal's comments 
OwnersVetID 
    INTEGER 
    Link to the owner's vet (owner table, 0 for no value) 
CurrentVetID 
    INTEGER 
    Link to the current vet (owner table, 0 for no value) 
OriginalOwnerID 
    INTEGER 
    Link to the original owner (owner table) 
BroughtInByOwnerID 
    INTEGER 
    Link to the owner who brought the animal in (owner table) 
ReasonForEntry 
    VARCHAR 
    Freeform text, reason animal was brought to shelter 
ReasonNO 
    VARCHAR 
    Freeform text, reason original owner didn't bring the animal in 
DateBroughtIn 
    TIMESTAMP 
    The date the animal was brought into the shelter 
EntryReasonID 
    INTEGER 
    Reason for entry (link to entryreason table) 
HealthProblems 
    VARCHAR 
    Health problems box on Vet tab 
PutToSleep 
    INTEGER 
    1 if the animal was euthanised 
PTSReason 
    VARCHAR 
    Freeform text, reason the animal died 
PTSReasonID 
    INTEGER 
    Euthanasia category - link to deathreason table 
IsDOA 
    INTEGER 
    1 if the animal was dead on arrival to the shelter 
IsTransfer 
    INTEGER 
    1 if the animal was a transfer from another animal shelter 
IsGoodWithCats 
    INTEGER 
    Link to the lksyesnoun table for yes/no/unknown 
IsGoodWithDogs 
    INTEGER 
    Link to the lksyesnoun table for yes/no/unknown 
IsGoodWithChildren 
    INTEGER 
    Link to the lksyesnoun table for yes/no/unknown 
IsHouseTrained 
    INTEGER 
    Link to the lksyesnoun table for yes/no/unknown 
IsNotAvailableForAdoption 
    INTEGER 
    1 if the animal should not be included for publishing as adoptable 
HasSpecialNeeds 
    INTEGER
    1 if the animal has special needs 
ShelterLocation 
    INTEGER 
    Location on shelter (link to internallocation table) 
DiedOffShelter 
    INTEGER 
    1 if the animal died off shelter (won't be included on figures reports) 
Size 
    INTEGER 
    animal's size (link to lksize table) 
RabiesTag 
    VARCHAR 
    The rabies tag field 
Archived 
    INTEGER 
    1 if the animal is off shelter now 
ActiveMovementID 
    INTEGER 
    Link to the adoption table for the latest movement for this animal 
ActiveMovementType 
    INTEGER 
    Link to lksmovementtype for the current movement type 
ActiveMovementDate 
    TIMESTAMP 
    The current movement date 
ActiveMovementReturn 
    TIMESTAMP 
    The current movement's return date 
HasActiveReserve 
    INTEGER 
    1 if the animal has an open reservation 
HasTrialAdoption
    INTEGER
    1 if the animal is on trial adoption
DisplayLocation
    VARCHAR
    Shows a readable version of the animal's location. If on shelter, the internal location. If off shelter, the movementtype, accompanied by the person name.
MostRecentEntryDate 
    TIMESTAMP 
    The most recent entry date - either DateBroughtIn or ActiveMovementDate 
TimeOnShelter 
    VARCHAR 
    Readable time spent on shelter (eg: 1 year, 3 months) 
DaysOnShelter 
    INTEGER 
    Number of days spent on shelter 
DailyBoardingCost 
    INTEGER 
    The cost of one days board on shelter for this animal 
AnimalAge 
    VARCHAR 
    Readable animal age (eg: 1 year, 3 months) 
RecordVersion 
    INTEGER 
    For optimistic locking, each save increments this lock value 
CreatedBy 
    VARCHAR 
    Name of user that created this record 
CreatedDate 
    TIMESTAMP 
    Date this record was created 
LastChangedBy 
    VARCHAR 
    Name of user that last changed this record 
LastChangedDate 
    TIMESTAMP 
    Date this record was last changed 

animalcontrol
-------------

The animalcontrol table holds a row for every animal control incident on the system.

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
IncidentDateTime
    TIMESTAMP
    The date and time of the incident
IncidentTypeID
    INTEGER
    A link to the incidenttype table for the type of incident
CallDateTime
    TIMESTAMP
    The date and time the incident was reported
CallNotes
    VARCHAR
    The incident description
CallTaker
    VARCHAR
    The username of the system user who took the call
CallerID
    INTEGER
    A link to the owner table for the person who called to report the incident
VictimID
    INTEGER
    A link to the owner table for the person who was the victim (if any)
DispatchAddress
    VARCHAR
    The address the incident occurred at
DispatchTown
    VARCHAR
    The incident town/city
DispatchCounty
    VARCHAR
    The incident county/state
DispatchPostcode
    VARCHAR
    The incident postcode/zipcode
DispatchLatLong
    VARCHAR
    A geocode containing the latitude and longitude of the dispatch address
DispatchedACO
    VARCHAR
    The username of the system user representing the ACO
PickupLocationID
    INTEGER
    A link to the pickuplocation table
DispatchDateTime
    TIMESTAMP
    The date and time the ACO was dispatched to the incident
RespondedDateTime
    TIMESTAMP
    The date and time the ACO arrived at the incident
FollowupDateTime / FollowupDateTime2 / FollowupDateTime3
    TIMESTAMP
    The date and time the ACO should return to the incident to follow up
FollowupComplete / FollowupComplete2 / FollowupComplete3
    INTEGER
    1 if the follow up has been done
CompletedDate
    TIMESTAMP
    The date the incident was closed and completed
IncidentCompletedID
    INTEGER
    A link to the incidentcompleted table
SiteID
    INTEGER
    A link to the site table for multi-site setups
OwnerID / Owner2ID / Owner3ID
    INTEGER
    A link to the owner table for the suspect
AnimalDescription
    VARCHAR
    A description of the animal involved in the incident
SpeciesID
    INTEGER
    A link to the species table for the animal involved in the incident
Sex
    INTEGER
    A link to the lksex table for the animal involved in the incident
AgeGroup
    VARCHAR
    The age group of the animal involved in the incident

dbfs
----

The dbfs table holds a row for each file stored in ASM's database. Name and
Path can be concatenated to form a full path. Eg: path = /templates name =
cage_card.html

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
Name
    VARCHAR 
    The name of the element
Path
    VARCHAR 
    The path to the element
Content
    CLOB
    Base64 encoded content

media
-----

The media table holds a row for each piece media attached to an animal, owner,
etc. 

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
MediaName 
    VARCHAR 
    The name of the file within the dbfs table - always [media.ID].extension 
MediaNotes 
    VARCHAR 
    The notes accompanying the media file (used as description when publishing) 
WebsitePhoto 
    INTEGER 
    1 if this is the preferred photo of an animal for use on the web 
DocPhoto 
    INTEGER 
    1 if this is the preferred photo of an animal for use with generated documents 
NewSinceLastPublish 
    INTEGER 
    1 if this media record was created after the last time a publish was done 
UpdatedSinceLastPublish 
    INTEGER 
    1 if this media record was updated after the last time a publish was done 
LastPublished 
    TIMESTAMP 
    The date this record was last published to the web 
LastPublishedPF 
    TIMESTAMP 
    Date this record was last published to PetFinder.com 
LastPublishedAP 
    TIMESTAMP 
    Date this record was last published to AdoptAPet.com 
LastPublishedP911 
    TIMESTAMP 
    Date this record was last published to Pets911.com 
LastPublishedRG 
    TIMESTAMP 
    Date this record was last published to RescueGroups PetAdoptionPortal.org 
LinkID 
    INTEGER 
    The ID of the record in the table this media is linked to 
LinkTypeID 
    INTEGER 
    Link to lksmedialink table to determine linked table 
Date 
    TIMESTAMP 
    Date this record was created 
RecordVersion 
    INTEGER 
    Optimistic lock flag 

owner
-----

The owner table holds a row for every person stored in ASM's database.

ID 
    INTEGER 
    A unique, incrementing number that identifies this record 
OwnerTitle
    VARCHAR
    The person's title, eg: Mr
OwnerInitials
    VARCHAR
    The person's initials
OwnerForeNames
    VARCHAR
    The person's first name(s)
OwnerSurname
    VARCHAR
    The person's surname
OwnerName
    VARCHAR
    Title, Forenames and Surname concatenated together for display
OwnerAddress
    VARCHAR
    The address
OwnerTown
    VARCHAR
    The town or city (depending on locale)
OwnerCounty
    VARCHAR
    The county or state (depending on locale)
OwnerPostcode
    VARCHAR
    
HomeTelephone
    VARCHAR
    
WorkTelephone
    VARCHAR
    
MobileTelephone
    VARCHAR
    
EmailAddress
    VARCHAR
    
IDCheck
    INTEGER
    1 if the person has been homechecked
Comments
    VARCHAR

IsBanned
    INTEGER
    1 if this person has been banned from adopting animals
IsVolunteer
    INTEGER
    1 if this person is a shelter volunteer
IsHomeChecker
    INTEGER
    1 if this person homechecks people
IsMember
    INTEGER
    1 if this person is a shelter member
MembershipExpiryDate
    TIMESTAMP
    The date this person's membership expires
MembershipNumber
    VARCHAR
    The membership number
IsDonor
    INTEGER
    1 if this person is a regular donor
IsShelter
    INTEGER
    1 if this person is another animal shelter
IsACO
    INTEGER
    1 if this person is an animal care officer
IsStaff
    INTEGER
    1 if this person is shelter staff (permissions exist to prevent users viewing staff records)
IsFosterer
    INTEGER
    1 if this person fosters animals
IsRetailer
    INTEGER
    1 if this person is a retailer for animals (eg: Pet shop)
IsVet
    INTEGER
    1 if this person is a vet
IsGiftAid
    INTEGER
    (UK only) 1 if this person is eligible for gift aid on their donations
AdditionalFlags
    VARCHAR
    Comma separated list of all person flags applying to this owner, includes additional person flags created under lookup data as well as the standard ones above.
HomeCheckAreas
    VARCHAR
    If this person homechecks other people, the areas they are wiling to check
DateLastHomeChecked
    TIMESTAMP
    The date this person was last homechecked
HomeCheckedBy
    INTEGER
    ID of the person record who homechecked this person
MatchAdded
    TIMESTAMP
    Date any looking for match was added to this person
MatchExpires
    TIMESTAMP
    Date this looking for info expires
MatchActive
    INTEGER
    1 If we should consider this person's looking for info when building the looking for report
MatchSex
    INTEGER
    
MatchSize
    INTEGER
    
MatchAgeFrom
    FLOAT

MatchAgeTo
    FLOAT

MatchAnimalType
    INTEGER

MatchSpecies
    INTEGER

MatchBreed
    INTEGER

MatchBreed2
    INTEGER

MatchGoodWithCats
    INTEGER

MatchGoodWithDogs
    INTEGER

MatchGoodWithChildren
    INTEGER

MatchHouseTrained
    INTEGER

MatchCommentsContain
    VARCHAR


