How to do Many to Many Relations in Mysql

Question

I have some trouble, Data Example: animal_table:

• id
• name
• desc

---

---

farmer_table:

• id
• name
• desc

---

---

investor_table:

• id
• name
• desc

---

Main Trouble: now i will create Simple CRUD applications, but there is some conditions. Investors(Many) to Animals(Many) Farmers(One) to Animals(Many)

how do i create the database Structure for this problems

Answer 1

In this answer it is shown what you need: How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

For your example it would be something like this:

Investors(Many) to Animals(Many) You need a need Table like investor_animal:

• investor_id
• animal_id

Farmers(One) to Animals(Many) You can give the animal table a farmer_id. So the animal knows who is the farmer.

Answer 2

The textbook solution to implementing a many-to-many relationship is a mapping table with the IDs of the entities you want to have in the relationship. Each row in this mapping table represents a connection between the two entities. E.g.:

CREATE TABLE investors_animals (
    animal_id INT NOT NULL,
    investor_id INT NOT NULL,
    PRIMARY KEY (animal_id, investor_id), 
    FOREIGN KEY (animal_id) REFERENCES animal(id) ON DELETE CASCADE,
    FOREIGN KEY (investor_id) REFERENCES investor(id) ON DELETE CASCADE
);