CODEWITHSUNDEEP
mysqllaravel-8
The Only Smart Boy
The Only Smart Boy

Reputation: 575

Database design for dynamic columns

I am trying to design a database in which users can set the cost of different products based on how much they sell them and their availability in their store. The users can only set their prices for the products and the availability but the products are created by the system admin for the users. I have tried by designing the database to have three tables one for the products and another for the users then the third is a modal for linking the users to the existing products using user_id and the product_id. What I am not able to do is to know how and where to set the price attribute of the products

Upvotes: 1

Views: 438

Answers (1)

Otema
Otema

Reputation: 153

As stated in the comment, using pivot tables then attaching all the additional data is the best option with mysql database in your case, you could have the users table with user_id as the primary key, products table with product_id as the primary key then create a modal table product_user with the fields of product_id and user_id as index, cost as decimal and availability or quantity. During insertion or update, you will use attach as follows

      $userProduct = Product::where(['id'=>$data['product_id']])->first();
    $userProduct->userProducts()->attach(User::where('id', $data['user_id'])->first(), ['amount' =>$data['cost'],'availability' =>$data['quantity']]);

In your Product model, you will need to create the userProducts() function that will hande the one to many relationship

Upvotes: 1

Related Questions