Help with Database Design - Products with Multiple Categories

Hey Guys,

I am quite new to building Laravel applications, so please pardon me if this is something very simple.

So I have two tables, namely Products (which contains product data, including categories) and Categories (which contains category title and id).

Now, a product can have multiple categories, what's the best way? Should I store category ids separated by commas in the products table? Should I be creating another table, say Product_Metadata, and have it there? What's the standard way of doing something like this?

Thank You

Sergiu17

4 years ago

Level 60

Hey, create a new table product_category

alexui

4 years ago

Level 1

@Sergiu17 Thanks for your quick reply, Sorry if I was not clear enough.

Here are the two tables:

Products: id title sales_price regular_price product_category

Product_category: id title

I am confused as to how to store multiple ids of the product category in the Products table

Sergiu17

4 years ago

Best Answer

Level 60

@alexui you need to add one more table, it's called pivot table, which will link products and categories table

------------------------------------------------
| products table                               |
------------------------------------------------
| id | title | sales_price | regular_price     |
------------------------------------------------

-----------------------
| categories table    |
-----------------------
| id | title          |
-----------------------

----------------------------------
| product_category table         |
----------------------------------
| id | prudct_id | category_id   |
----------------------------------

check domentation for many to many relationship https://laravel.com/docs/9.x/eloquent-relationships#many-to-many

1 like

Please or to participate in this conversation.