My intentions to design the database schema for the small pet vaccination application. Inside Pet table I want to store details about the pets and if specific vaccination is required. There are 3-4 vaccination types(VT1, VT2, Vt3, VT4), which is stored inside Vaccine_Type table. For each vaccination type there are vaccines from various vendors, so I decided to store that information inside Vaccine table. Vaccination details are stored inside associative table - Vaccination. Inside which I have: pet_id, vaccination_type, vaccine_id, veterinarian_id, vaccination_date and expiation_date, if expiration date is over active field is set to False, indicating that vaccination is no more active and requires new vaccination. Kindly advise how can link this information into Pet table, to show which vaccination Type requires revaccination. And advice if the database schema is correct , or could be optimized more.

CodePudding user response：

I would modify a little bit.

• Vaccine_Type does not need to be linked to Vaccination. The type can be derived from the link between Vaccine and Vaccination.
• No need for an expiration_date or active boolean in the Vaccination table.
• In the Vaccine table, add an "active period" field, that defines how many years, month or weeks does the vaccine remain active (or however the manufacturers specify such period).
• Add a Species table. Ex cats, dogs, ... This table to be linked to the Pet table and the Vaccine table. Vaccines are specific to species, or if they are general, add it to a link table between the two. This table will be useful later.

When you want to check if a pet has any expired vaccinations, do the following:

1. Get the list of vaccines that apply to the pet's species via the Vaccines table and Species table.
   
2. Get the latest date that each Vaccine was injected to the Pet via the Vaccination table. That value might be null if it was never done. In that case, go to step 5 below.
   
3. Get the active period for the vaccines from the list of vaccines you extracted above.
   
4. Compare the (date from step 2 period from step 3) with the current date.
   
5. If it is expired, schedule a new injection via your scheduling application.

As a general concept, store data in the database. Anything that requires to be calculated does not go in a field, it goes in the application, using the data in the database.

Another concept, do not link things that can be derived from other links. This respects the normal forms.

So:

Pet
    petid, PK
    name, NN
    speciesid, FK (Species.speciesid), NN
Species
    speciesid, PK
    name, NN

Vet
    vetid, PK
    name, NN
    
Vaccine
    vaccineid, PK
    name, NN
    vaccinetypeid, FK (Vaccinetype.vaccinetypeid), NN
    activeperiod, NN
Vaccinetype
    vaccinetypeid, PK
    name, NN
Vaccine_Species
    vaccineid, FK (Vaccine.vaccineid)
    Species, FK (Species.speciesid)
    
Vaccination
    vaccinationid
    vetid, FK (Vet.vetid)
    petid, FK (Pet.petid)
    vaccineid, FK (Vaccine.vaccineid)
    date, NN
    

Legend:

• PK: primary key
• FK: foreign key
• NN: not-null contraint

Notes:

• can a vaccine be of multiple types? If so add a link table instead of a single FK.
• If it is impossible for a vaccine to apply to multiple species, you can do a direct link between tables Vaccine and Species instead of a link table.