mysql - There is a many-to-many relationship between one table and multiple tables. How to design it?

Question

Recently encountered a problem related to a table and multiple tables,

For example: Now there is an application table app_table, and there are many material tables material_table1, material_table2...,
For each material table, one material may be used by multiple applications, and one application may also use multiple material. Each material table and application table have this relationship, and there is no association between each material table.

Obviously it is many-to-many, but the problem is that if the table is created according to many-to-many, an intermediate table must be created for each material table.

I now have an idea, which is to add fields to the application table, and add a field for each material. The field stores the IDs of the materials
owned by this app, separated by commas. But the problem is that in this case, you need to query twice, first filter through the fields of the application table, and then filter the queried data according to the conditions.

I don’t know if you have any better plans or ideas, thank you all

---

The description is a bit unclear. Each category of materials (a material table) has an interface. However, when the materials are returned, they must be filtered according to the application, and there is an application that uses multiple materials (one material table). Multiple materials in the table), one material may be used by multiple applications. The current status is that each material table has an application field added to distinguish it, but this requires adding a lot of entries. So I considered whether to make an application table, and then make an association table for each material. In this way, when making a request, you can first look up the data in the application table based on the application name of the request parameter, and then look up the qualified data in the relevant material table based on the association.
I don’t know if there is a better way.

Answer 1

First of all, there is something wrong with the design of your table structure. Multiple materials, why do you need multiple material lists on your resume? You can use the material type to distinguish it.
I don’t know why you want to divide the materials into tables. If so, I guess it’s because the types of materials are different. I think the table should look like this

app application table
material material table
material_type material type
app_material material application relationship table

Answer 2

I feel like I only need one association table:

Association table
Application ID Material table ID Material ID
01 07 08

You can determine which materials are used by an application

Answer 3

Complain: Where does the many-to-many come from? The fields of each material table are different. The application table has a many-to-many relationship with a certain type of material table (elements of the material table), but the application table has a many-to-many relationship with all material tables. It's not a many-to-many relationship, it's a relationship between inclusion and non-inclusion.

Answer 4

First of all, according to your idea, your data table will be very large and difficult to maintain in the future. It is recommended that you convert the properties of the materials into json or serialize them for storage.

Answer 5

For one-to-many relationships, we generally use an intermediate table, and only for one-to-one relationships, a column will be added to represent the relationship

Answer 6

A A_ID A_OTHER
B B_ID B_OTHER
C C_ID C_OTHER
REF REF_ID(序列) A B C D E …

1                        
2                        
3                        
4                        
5                        

Answer 7

app_id | material_table_name | material_table_id

Keyword, polymorphic association