Postgres foreign key to multiple tables

Question

I have the following conceptual problem for whom I haven't been able to come up with a satisfying solution yet. I'll explain it with an example.

I have 2 tables, cats and dogs, which are made up of different attributes (they need to be different tables as my real case is actually more complex). I can feed these 'animals' and I want to keep track of some info about this feeding operation (say [animal_fed], food_type, food_quantity, date, ...).

By now I am using a feeds table with the following schema: animal_id INTEGER, table_name VARCHAR(50) (could be "cats" or "dogs", but there gonna be lots of more species..), [other fields].

I'am completely unsatisfied with this solution cause it makes it a pain (if even possible, but a way there must be..) to select from feeds retrieving also some info on the animal been fed (similarly to join).

How can I better approach this problem?

Would using a 'parent' table represent a good solution?

Or if you think my actual approach is good how can I make a "join" to get, say, all the feeds info plus the animal name?

Thank you.

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 will have the animals table ane the feeds table and you create r_dog_feed and r_cat_feed. 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, food.* 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, food.* FROM r_cat_feed JOIN catsON dogs.id = r_cat_feed.cat_id JOIN feeds ON feeds.id = r_cat_feed.feed_id