Postgres foreign key to multiple tables
foreign-keys, postgresql, sql
Solution
There are several ways to improve it, but your solution is nearly good:
- 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.
- 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.
- 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
Problem
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.