CODEWITHSUNDEEP
javamysqlmany-to-many
alireza azadi
alireza azadi

Reputation: 77

Correct way to design Many To Many Relationship

I am developing a software in Java programing language and use MySQL as DB. This software collect some input from user, and then calculate/select the product he/she needs. First of all, he/she create a project(A table named project should be created) and calculate/select products,after that add them to the project. The product fields are different from another.( so we have different tables for each product named A, B, C ) Ex, I have 3 type of products, that each of them has specific fields and differ from another, thus the way user select/calculate each of them is different. Product A has 3 fields ( product_id, name, model ) Product B has 7 fields ( product_id, name, color, company name, .... ) Product C has 14 fields (product_id, name, company name, made by, created by, ..... ) I can not consider these products as one, cause the fields are different. Each products can be in several project, and each project can have several type of products.( Many to Many relationship ) Every project must save the products. In this situation, I should create many to many relationship between every product table and project? If yes How can I retrieve project again later? What is best practice for this situation? How can I consider these 3 products table as one?

Upvotes: 0

Views: 90

Answers (1)

ChrisFNZ
ChrisFNZ

Reputation: 617

You might want to create a JOIN table to manage the inter-relationship between records in your other two tables.

For example:

  • project (project_id, project_name)
  • product (product_id, product_name)
  • project_products (pp_project_id, pp_product_id)

That way you can:

  • Create projects and products before you define their inter-relationships
  • Easily define the inter-relationship between projects and products without creating a complex schema

Usage example:

projects table:

project_id  |  project_name
------------+----------------
 1          | Country market
 2          | City carnival

products table:

product_id   |  product_name
-------------+---------------
 101         | Candy floss
 102         | Cheeseburger
 103         | Hamburger
 104         | Pizza - Hawaiian

project_products table:

pp_project_id  |  pp_product_id
---------------+----------------
1              | 101
1              | 103
1              | 104
2              | 101
2              | 102
2              | 104

In the example above...

  • Project 1 has 3 products against it
  • Project 2 has 3 products against it
  • Products 101 and 104 are both recorded against 2 projects
  • The remaining products are recorded against 1 project only

Upvotes: 1

Related Questions