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 lksynun table for yes/no/unknown
IsGoodWithDogs
INTEGER Link to the lksynun table for yes/no/unknown
IsGoodWithChildren
INTEGER Link to the lksynun table for yes/no/unknown
IsHouseTrained
INTEGER Link to the lksynun 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