Table-related issues

There are three tables, which are album table, product table, Picture table, respectively.
Requirements: Albums can be stored in both products and pictures.
How would you like to express the relationship between the three people?

I analyzed that albums and products are many-to-many relationships, and albums and pictures are many-to-many relationships.
Do you want to design two intermediate tables?

Reply to discussion (solution)

You can create a table between the album, the product table, and the Product ID album ID (separated by commas)

Pictures and albums in the same vein

As mentioned above, the Product ID album ID, it is best to have each table, so you can relate to each other.

You can create a table between the album, the product table, and the Product ID album ID (separated by commas)

Pictures and albums in the same vein
I have thought about this method, if you want to query the same album products and pictures to separate query?

If you are saving a comma-connected product ID and a picture ID, then there is no need to create another table.
The purpose of the transition table is to get high-speed access, and if you connect the target ID to a string, you can't use the index.

If you are saving a comma-connected product ID and a picture ID, then there is no need to create another table.
The purpose of the transition table is to get high-speed access, and if you connect the target ID to a string, you can't use the index.
My intention was to create a third transition table. It's just the relationship between the three, how to design the transition table.

Feature ID Product ID Image ID

In general, the product and picture one by one correspond
So the table's sparsity is not too large.

Feature ID Product ID Image ID

In general, the product and picture one by one correspond
So the table's sparsity is not too large.

In my system, products and pictures are two entities. divided by two tables. This picture is not a product picture.

then separate it.
Feature ID Product ID
Feature ID Image ID

Since it's two entities, why do you have to pull it together? Do you want to design two intermediate tables?

then separate it.
Feature ID Product ID
Feature ID Image ID

Since it's two entities, why do you have to pull it together? Do you want to design two intermediate tables?

Because these two entities can be stored in the album. If both entities are present in the same album. How to Query

SELECT * from A LEFT join A_b on A.id=a_b.aid left join A_c on A.id=a_c.aid

SELECT * from A LEFT join A_b on A.id=a_b.aid left join A_c on A.id=a_c.aid

Now all you're saying is a joint query of the album table and the two intermediate table A_b,a_c. But the intermediate table is just the primary key of the two tables, if you want to look up specific information, the Comparison form table and the product table information. is not going to continue to the left JOIN graph table, left JOIN product table.

That's for sure!
But you just need to INNER JOIN.

That's for sure!
But you just need to INNER JOIN.
OK, thank you, Moderator's help!

