Other field Determination

[Garret]

Imagine this situation...there are:

Owners (tblOwners)
Pets (tblPets, child records of tblOwners)
Medicines (tblMeds, child records of tblPets)

Owners can have multiple pets.
Pets have multiple medicines. Easy so far.

Here's the tricky part. The Pets can be of different species (cat,
dog, bird, snake, etc). Depending on what the species is, I want to
determine the other data that needs to be stored for the pet. So
instead of just height, weight, age, as fields in the pet table, I
want to see number of whiskers, declawed? if its a cat...wingspan,
beaklength, feather color if its a bird...etc for other animals.
Normally I would think because of this situation, there would have to
be a table for each type of animal. However, all pets can have
medicines and I don't want to have several medicine tables, how can I
establish tables and relationships to have this effect?
(And for a bonus, how could I have this display on the forms so the
user could select an animal type, and the corresponding data would
appear to be entered in?)

 

[Guest]

Here is a link to a data model. You might be able to get some ideas.
http://www.databaseanswers.org/data_models/vets_practice/index.htm

 

[Garret]

Thanks, Eric. This link was extremely useful in teaching me more
about Database design, and I've been looking around the site just to
learn as much as I can.

However, I don't think this addresses the problem I have at hand.
None of the examples (that I have seen) that the site shows have -
situational tables- like I want.

Do I just have a problem with my table design?

 

[Garret]

Also note, to make this problem easier, and also more realistic to
what my database will use, the different kinds of "animals" will only
ever be around 4-6. The ability to have more whenever I desire would
be nice, but not necessary to finishing the database.

 

[Garret]

I think my problem can be solved by using some kind of "Inheritance".
This way there can be a parent animal table with height, weight, age,
and child tables with more specific data depending on the animal. How
is this done in Microsoft Access? ( I have version 2000 by the way).