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 EventID INTEGER Link to the event table for the adoption event this movement came from 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) InsuranceNumber VARCHAR If your shelter does short term insurance when adopting, the policy number 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 ReturnedByOwnerID INTEGER Link to the owner table for the person who returned the animal 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 IsPermanentFoster INTEGER 1 if this foster movement is permanent 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 Adoptable INTEGER 1 if the animal is adoptable according to publishing rules 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 URL VARCHAR Either file:[filename] or s3:[filename] for binary data stored in the filesystem or remote object storage like Amazon S3, BackBlaze B2 or Cloudflare R2 etc. If the data is in the Content field, the URL will be contain the text base64: Content CLOB Base64 encoded content or null if the data is in a file storage (see URL) 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 MediaType INTEGER 1 = File, 2 = Link MediaName VARCHAR The name of the file within the dbfs table - always [media.ID].extension unless MediaType=2 in which case this will hold the link URL MediaNotes VARCHAR The notes accompanying the media file (can optionally be used as description when publishing) DocPhoto INTEGER 1 if this is the preferred photo of an animal for use with document templates WebsitePhoto INTEGER 1 if this is the preferred photo of an animal for use on the web WebsiteVideo INTEGER 1 if this is the preferred video link of the animal DocPhoto INTEGER 1 if this is the preferred photo of an animal for use with generated documents ExcludeFromPublish INTEGER 1 if this photo should be excluded from being used on websites or sent to third parties SignatureHash VARCHAR For media containing documents that have been signed, this field will contain a cryptographic hash of the document file data so that it can be checked for tampering NewSinceLastPublish (deprecated) INTEGER 1 if this media record was created after the last time a publish was done UpdatedSinceLastPublish (deprecated) INTEGER 1 if this media record was updated after the last time a publish was done LastPublished (deprecated - see animalpublished table) TIMESTAMP The date this record was last published to the web LastPublishedPF (deprecated) TIMESTAMP Date this record was last published to PetFinder.com LastPublishedAP (deprecated) TIMESTAMP Date this record was last published to AdoptAPet.com LastPublishedP911 (deprecated) TIMESTAMP Date this record was last published to Pets911.com LastPublishedRG (deprecated) 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 CreatedDate TIMESTAMP The date this record was created Date TIMESTAMP Date this record was last updated RetainUntil TIMESTAMP The date to automatically delete this record (or NULL to never delete) 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 OwnerType INTEGER 1 = Individual, 2 = Organization, 3 = Couple OwnerCode VARCHAR A unique code for this record, generated from first two letters of surname and padded ID OwnerTitle / OwnerTitle2 VARCHAR The person's title, eg: Mr OwnerInitials / OwnerInitials2 VARCHAR The person's initials OwnerForeNames / OwnerForeNames2 VARCHAR The person's first name(s) OwnerSurname / OwnerSurname2 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 / WorkTelephone2 VARCHAR MobileTelephone / MobileTelephone2 VARCHAR EmailAddress / EmailAddress2 VARCHAR DateOfBirth / DateOfBirth2 TIMESTAMP IdentificationNumber / IdentificationNumber2 VARCHAR The government issued identification number (passport, driving license, etc) Comments VARCHAR GDPRContactOptIn VARCHAR didnotask,declined,email,post,sms,phone ExcludeFromBulkEmail INTEGER 1 if this person should not receive bulk emails IDCheck INTEGER 1 if the person has been homechecked 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 IsDriver INTEGER 1 if this person is a driver for transport 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 MatchFlags VARCHAR