A Pet Care Data Model

Pet care is a huge industry. Is there a data model that can help pet owners and professionals manage their activities? There is now!

Many people share their lives with cats, dogs, birds, and other animals. (I once had a pigeon for a while, until its wing mended.) What a lot of pet owners don't realize is just how big a business pet care is. In the United States, pet owners spent $66.75 billion – and that was just in 2016!

While most of us can keep our hamsters alive without using sophisticated technology, there are plenty of businesses that center around pet care: pet kennels (a.k.a. pet hotels or pet resorts), pet groomers, pet sitters (who stay in your home, with your pet, while you go on vacation), dog walkers, pet behaviorists, even pet masseurs and therapists. These often provide fairly complex services to pets and their owners, and they would need a data model to keep them organized. So let’s take a look at one.

What Goes into a Pet Care Data Model?

Before we start describing the model’s details, let’s discuss some requirements:

1. Who will need this data model?

   Although this data model might sound exotic, it’s not really that unusual. Just imagine yourself running any of the businesses mentioned above. No matter how different these business models are, you’d still need to:

   • Communicate with potential clients
   • Explain your services and state their prices
   • Organize your schedule
   • Track in-progress tasks and activities
   • Charge clients for services rendered

   So yes, there is a chance you’ll need this model for yourself or for your clients.

   Now we can move on and answer some technical questions.

2. What should be covered in this model?

   It will be general enough to cover many different situations. I’ll go with the assumption that we’ll have a physical place where we’ll provide services (like a pet hotel) or that serves as a starting point for providing services (i.e. for a dog walker).

   We’ll also need to store records for individual pets and their owners, as well as records of the services we provide. Relating all of that should cover most pet-care cases.

3. Why is this model important?

   To explain, let me tell you about a “prophecy” that I think will come true.

   We’re all aware of how technology is changing business. We see articles about jobs that automation will take over in the next 10 or 20 years. Most of these jobs will probably be ones that don’t have depend on contact with humans. For example, many stores have self-checkout lanes now where one human employee can monitor 5 or 10 checkouts. Before, each of these checkouts would have had a cashier. But waiting in line to pay for your groceries is probably not the best moment of your day. And that job is also very tiring and underpaid, so cashiers are not really excited about seeing you. These kinds of jobs can and are being automated.

   The other set of jobs that will be automated are intellectually more challenging but somewhat repetitive – e.g. almost all financial services, most computer programming, and even writing.

   So, my “prophecy” is that jobs that require a lot of human (or, in this case, pet) contact will not only survive but become “jobs of the future”; we’re talking about psychologists, hair stylists, dog groomers, and pet sitters, etc. But they’ll need some technology to run their businesses. And that’s where this model comes in.

The Data Model

This data model consists of four subject areas:

• Pets
• Facilities & services
• Cases
• Planned & provided

We’ll start with the Pets area because pets are obviously the most important part of this business model. After that, we’ll continue in the same order as the subject areas are listed.

Section 1: Pets

I’ll start with the Pets subject area; after all, this model is here because of our little friends dressed in their furs and feathers. I’ll keep it simple, although this subject area could be expanded. For instance, we could store many more details describing pets, their characteristics, and the pet owners (and their characteristics 😊 ).

The most important table in the whole model is the pet table. For each pet, we’ll store:

• name – The name the owner gave their pet.
• species_id – References the species dictionary and denotes the pet species.
• birth_date – The pet’s birth date, if available.
• notes – All additional notes related to this pet, in free text format.

In the owner table, we’ll store a list of all our past, current, and potential clients. Personally, I don’t like the word “owner”, because after you live with your pets they are more like family members. But I’m okay to use it in the data model. So, for each owner, we’ll store their first_name and last_name, contact details (as we know them, we may not know them all) and any additional details in the notes attribute.

We’ll relate owners and pets using the pet_owner table. One owner could have many pets and one pet could have a couple of owners, so we’ll need to insert a many-to-many relation here. For each record, we’ll store a UNIQUE pet_id – owner_id pair.

The third and last table in this subject area is the species dictionary. Besides the primary key attribute id, it contains only the UNIQUE species_name value. We’ll use this dictionary to store the species information at the level the business requires. We could go with a set of simple values like “cat”, “dog”, “horse”, and “bird”. Or we could use more descriptive values like “cat – Maine Coon”, “cat – Munchkin”, etc. We could employ a more complex structure – i.e. having one table for species and another for breeds – but I don’t think this approach will bring anything new to the model.

Section 2: Facilities and Services

The second most important thing in this model are the services we’ll provide. We’ll need facilities, too, no matter what we offer pet owners. This could be one place, such as a pet hotel, or it could be a place where we pick up or drop off pets (such as a dog walker would use). We will store this information in the Facilities & services subject area.

I’ll start with the service table. This is a dictionary we’ll use to store a list of all services we offer to our clients. For each service, we’ll need a:

• service_name – A name that UNIQUELY defines a service.
• has_limit – A value that denotes if this service has a limit (e.g. the number of “beds” in the pet hotel).
• unit_id – The unit we’ll use to measure that service. It depends on the type of service we provide and if it requires time or material (or both). In most cases, we will be concerned about time. For example, if a dog stays in a pet hotel, then the unit used should be a “day”. On the other hand, if we are walking a dog, then the unit should be an “hour” or a “minute”. Besides time units, we could also use other measures, e.g. if we want to define the number of treats that the dog will be “provided”.
• cost_per_unit – The current cost per unit for that service.

The unit dictionary is used to store the list of UNIQUE unit_name values. Values from this dictionary are referenced only in the service table, but they are very important in the planning phase and when we charge clients for services provided.

For each service, we’ll also need to define every accepted species. For example, maybe we’ll provide pet hotel service only for cats and not for dogs. This may be the case regardless of the fact that we offer dog walking and grooming. We’ll store all the UNIQUE service_id – speices_id pairs in the available_for table.

After we have described all our services and their details, now we’ll describe the facilities (places) where we’ll provide these services.

There is a good chance that we’ll operate more than one facility and provide more than one service. Because of that, we’ll need to store all our facilities and their related details. We’ll use the facility table to track the:

• facility_name – A name we’ll use internally to UNIQUELY denote that facility.
• address, phone, email and contact_person – Location and contact information, which are pretty much self-explanatory.

For each facility, we’ll store which services it provides. We could have one facility for cats only and another for dogs only. Or we could have a veterinary technician in one facility and not in the other. In any case, we’ll need to store all services we are able to provide in each facility. In the provides table, we’ll store a UNIQUE facility_id - service_id pair. In case that service.has_limit for the referenced service is true, we’ll also need to define the service_limit for that facility as well the currently_used quantity. That value should be recalculated each time we start providing that service for one more pet in that facility (e.g. one more place in the pet hotel is taken) or we stop providing it to a pet (e.g. the number of available pet beds in the hotel has increased by one).

Section 3: Cases

The Cases subject area is where we’ll describe and store all data related to visits or sessions (i.e. one visit is one stay at our dog hotel, one grooming, one walk. etc.)

The case table stores pets and facilities related to sessions, calls, or visits. I’ve decided to use “case” as the name of the table because we may not store only visits here. Maybe we want to store calls or other contacts. For each case, we’ll store:

• facility_id – The ID of the related facility. That could be where the pet stayed (in a hotel) or the facility that received a call related to this case.
• pet_id – The ID of the pet involved.
• start_time – The actual timestamp when that case started.
• end_time – The actual timestamp when that case ended. It will be NULL until the case is closed.
• notes – Any additional notes, in textual format, related to that case.
• closed – If this case is closed or not. It will be set to “True” when the end_time is set.

We’ll use the combination of facility_id – pet_id – start_time as the UNIQUE key of this table.

Each case will have one or more statuses assigned to it. We can expect that the first status assigned will indicate when the case started. After that, we’ll assign new statuses as needed, until the case is resolved (closed).

The first dictionary here is the status_category dictionary. It contains a list of UNIQUE status_category_name values. These are group status by type, e.g. “physical status”, “appetite”, or “general status”.

All possible statuses are stored in the status dictionary. For each status, we’ll store its status_name, the ID of the status category it belongs to, and the is_closing_status value. If the is_closing_status value is “True”, it means that when we assign this status, the case will be marked as closed. The status_name – status_category_id pair forms the UNIQUE key of this table.

In the case_status table, we’ll store all statuses that were actually assigned to cases. For each record in this table, we’ll store references to the case and the status tables, any additional notes, and the insert_time of that status. We could, for example, store a pet’s current physical condition and appetite as statuses when the pet comes into our facility. These statuses would be changed if we noticed a change in their condition. On the other hand, we’ll also store statuses regarding each case (e.g. “dog was walked”); we’ll put any additional details regarding that status in the notes attribute. These statuses won’t be “closing” statuses because they are related to a) current status of the pet at that moment, or b) to actions taken during the session or visit. An example of a “closing” status could be, “dog checked out” of our pet hotel.

The last table in this section is the note table. We’ll use this table to store all notes related to cases when there is no need to insert new status. For each record, we’ll store the note_text, an ID of the related case, and the insert_time when that note was created.

Section 4: Planned and Provided

The final subject area is the Planned & provided subject area. The three tables in this subject area store data about services we planned to provide, those actually provided, and all invoices related to cases.

The service_planned table contains a list of all services we have proposed to our clients or that they have reserved. Each record will contain the:

• case_id – The ID of the related case.
• service_id – The ID of the related service.
• planned_start_time & planned_end_time – When we plan to start and end this service. The start time should be defined but the end time can be NULL.
• planned_units – The number of service units planned, e.g. 3 days in a pet hotel.
• cost_per_unit – The cost per unit at that time. It’s important to store this value because the value stored in service.cost_per_unit could change between the time the reservation is made and the time it is performed.
• planned_price – The price quoted for that service. This should be equal to planned_units * cost_per_unit.
• notes – Any additional notes related to the planned service.

The service_provided table has almost the same structure as the service_planned table. The only difference is that the units and price_charged attributes could contain NULL values. This is due to the fact that we can insert a record in this table when we start providing the service (e.g. when the pet enters the pet hotel) and we’ll update them when we stop providing the service (e.g. when the owner takes the pet home).

The last table in our model is the invoice table. It keeps a list of all invoices we have generated for all our cases. For each invoice, we’ll store:

• invoice_code – An internal UNIQUE number generated for each invoice.
• case_id – The ID of the related case.
• time_generated – When the invoice was generated.
• invoice_amount – The original amount we’re charging to the client. This amount should be equal to the sum of all values in price_charged for service_provided.
• discount – A discount given to the client (e.g. because of a coupon, loyalty card, etc. The reason doesn’t really matter.)
• time_charged – When the client was actually charged for that invoice. This attribute will contain a NULL value until payment is made.
• amount_charged – The actual amount that was charged to the client for that invoice.
• notes – Any additional notes related to that invoice.

What Would You Add?

Today we talked about a possible data model for a pet care business. This model covers the basic functionalities, but there’s room for improvement. Please share your suggestions with us in the comment section. Thanks!