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