Hi all, I'm new database design and I run into a problem while doing my project.

May I know what is a more effective way of designing a product listing database where each category can have mulitple products and a product can appears under multiple categories?

I figured if I limit the number of categories, I can easily solve the issue, however the project requires us to let user create unlimited categories.

Any help would be much appreciated.
Thanks!

a table for categories, a table for products, then a third table, the relationship table, which associates products and categories

there can be multiple rows in the third table for a single product, and multiple rows for a single category

this third table will have only two columns, the product_id and the category_id, each of which is a foreign key to its respective table

together, the two columns form a composite primary key (so that you cannot put the same product into the same category moire than once)