foreign key to multiple tables

Question

Tables cats and dogs are made up of different attributes. I want to keep track of feeding (say animal_fed, food_type, food_quantity, date, ...).

Table feeds has animal_id INTEGER, table_name VARCHAR(50) (could be cats or dogs, but there will be more species) and other fields.

It is a pain (if even possible) to select from feeds retrieving also informaiton on the animal been fed (similarly with join).

How can I better approach this?

Is using a parent table good?

Or if my approach is good how can I join to get, say, all the feeds information plus the animal name?

Answer 1

There are several ways to improve it, but your solution is nearly good:

1. Put all animals in one table. You said they need to be different, but maybe you can merge them. Final animals table will have all columns from both table. And you will have column animal_type which will be either dog or cat.
2. Multiple SQL queries. You leave everything as it is and make some method/function to get data you need. Make a query to get all feeds and then in another query you can get those animals. If you want to limit the query you can add condition FROM animals WHERE id IN ( ANIMAL_ID_FROM_PREVIOUS_RESULT ). Then you have to merge these in your application.
3. You can make two relation tables. You still have the dogs table, the cats table and the feeds table and you create r_dog_feed and r_cat_feed tables. They will have feed_id which will refer to feeds table and then either dog_id or cat_id. Then you can write SQL using UNION:

SELECT dogs.name as name, feeds.* FROM r_dog_feed
    JOIN dogs ON dogs.id = r_dog_feed.dog_id
    JOIN feeds ON feeds.id = r_dog_feed.feed_id
UNION
SELECT cats.name as name, feeds.* FROM r_cat_feed
    JOIN cats ON cats.id = r_cat_feed.cat_id
    JOIN feeds ON feeds.id = r_cat_feed.feed_id