Question 20810 in Mysql Question posted in
The official MySQL documentation can be found here.

How to do Many to Many Relations in Mysql

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search