SQL and relations between tables

Question

For serveral times I've been returning to understanding of database relational theory, and still I don't have success. I'll try once more.

Let's say I have two tables: animals:

CREATE TABLE animals (id INTEGER PRIMARY KEY, name TEXT);

and food:

CREATE TABLE food (id INTEGER PRIMARY KEY, food TEXT);

What I need is to make this two tables connected. For example, I want to select 'pig' from animals table and recive all the things the pig can eat from food table. I just don't get how to relate them. I belive I can add a foreign key to food table, which would link to the primary key of animal table, but there is an issue I can't figure out:

What if I make entries to the database from, for example, a web form, where I enter animal name and product which it eats. The animal name goes to the first table and automatically recieves an id. It just autoincrements. So, in order to make it a relation for the second table I must to select the new ID from the first table! So we got THREE sql requests:

1) INSERT INTO animals (name) VALUES ('pig);

2) SELECT id FROM animals WHERE name='pig'; (we store it in a variable, does not really matters for now)

3) INSERT INTO food (product, animal_id) VALUES ('something', 'id of a pig');

I just feel that it is wrong. Or my mind is just not capable of understanding such complex abstractions.

Please advice.

Well, Postgress can into arrays, and this would be much easier, but what I want is, well, generic way, which would work with all database MSs. — Ivan, Jul 20 '15 at 11:25

score 3 · Answer 1 · answered Jul 20 '15 at 11:06

You need a junction table, that related Animals and Food. This would look like:

CREATE TABLE AnimalFoods (
    id INTEGER PRIMARY KEY,
    AnimalId int references Animal(id),
    FoodId int references Food(id)
);

You can then answer your questions using various joins among these tables.

score 1 · Answer 2 · answered Jul 20 '15 at 11:05

Can an animal eat multiple foods? If not, then you can have animal be the primary key on food the food table. If animal can have multiple foods, then you can have an auto increment ID (just like the one in the animal table) in the food table. As jWeaver pointed out, have a parentID in the food table as the foreign key referencing the animal table.

score 1 · Accepted Answer · edited May 23 '17 at 12:16

That's how you implement such a many-to-many relationship:

How to implement a many-to-many relationship in PostgreSQL?

And you can accomplish the task you describe with a single query using a data-modifying CTE:

WITH ins AS (
   INSERT INTO animals (name) VALUES ('pig')
   RETURNING animal_id  -- return generated ID immediately
   )
INSERT INTO animal_food (food_id, animal_id)  -- m:m link table
SELECT food_id, animal_id  -- food_id passed as 2nd param
FROM   ins;

Assuming we operate with a known-existing food (like it was select from a drop-down menu. Else you need one more step to look up the food or possibly INSERT a row there, too:

Is SELECT or INSERT in a function prone to race conditions?

... still a single query.

The linked answer provides some insight in the more tricky matter of race conditions with concurrent transactions.

score 0 · Answer 4 · answered Jul 20 '15 at 11:02

0

Create an animal table with parentId and then in your food table use that parentId column to refer.

Example:

Animal(ParentId integer, NAME TEXT);

and

Food(FoodId integer, ParentId integer, Name Text);

That's it. Make sure, you are using same ParentId in food table to refer food for specific animal.

Hope, this make sense to you.

answered Jul 20 '15 at 11:02

Ravi

28,657
41
110
158

But how do I generate this ParentID when I add entries to the database? – Ivan Jul 20 '15 at 11:07
which RDBMS are you using ? – Ravi Jul 20 '15 at 11:08
sqlite, mysql, whatever. – Ivan Jul 20 '15 at 11:22

score 0 · Answer 5 · answered Jul 20 '15 at 11:07

If different animals eat the same kind of food, you should define a table with foreign keys the id of the two related tables:

CREATE TABLE animals (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE food (id INTEGER PRIMARY KEY, food TEXT);
CREATE TABLE eat (animal_id INTEGER, food_id INTEGER,
     FOREIGN KEY (animal_id) REFERENCES animals(id),
     FOREIGN KEY (food_id) REFERENCES food(id));

score 0 · Answer 6 · answered Jul 20 '15 at 11:16

Assuming an animal would eat multiple food and multiple animals could eat same food. You need to have a many-to-many association with animals and food.

Animal(id integer, name Text)
Food(id integer, name TEXT)
AnimalFood(animalId integer,foodId integer)

CREATE TABLE animals (id int(11) not null auto_increment primary key, name text);
CREATE TABLE foods (id int(11) not null auto_increment primary key, name text);
CREATE TABLE animal_foods (animal_id int(11) not null, food_id (11) not null);

SQL and relations between tables

6 Answers6