Dosmukhanbet's avatar

Need your advice on the structure of database tables and relationships

Hi, Can anyone suggest if the table and relationship structure in my database is correct in the following situation: I am implementing a website with multiple stores, which basically have the same product in stock. Since the items in different stores are the same, but the price and quantity in stock in the stores are different, I did the following:

  1. I created a table of products with name, description, picture.
  2. I created a store table with store information
  3. I created an intermediate table shop_stock with shop_id and product_id columns and Price and quantity columns which are related to the store.

To get the price and quantity for a particular product from a particular store, I did the following:

Shop::with(['products' => function (Builder $query) use ($product_id) {
            $query->where('id', $product_id);
        }])->findOrFail($shop_id);

class Product extends Model
{
protected $fillable = [
'name',
'description',
];

}
class Shop extends Model
{
protected $fillable = [
'user_id',
'name',
];

public function products()
{
return $this->belongsToMany(Product::class, 'shop_stock')->withPivot('price', 'balance');
}

}


0 likes
2 replies
Tray2's avatar

That looks pretty good I think, at least from the info you told us.

martinbean's avatar

@dosmukhanbet Looks alright. Questions I’d have are:

  • Who controls the catalogue (products) if a single Product model is shared by multiple stores? Do stores add/edit products? Or are products controlled by you? I’m just thinking, if stores did control products, what happens if Store A updates the name of Product X to something inappropriate. Is that inappropriate name going to then start showing on everyone else’s stores?
  • How are you envisaging stock working if it’s in a pivot table? What happens if Store A has stock of 100 for Product A, Store B has stock of 100 for Product A, but there’s only actually 150 in stock of Product A in total? Product A is going to potentially get oversold, and there’s going to be customers who have ordered 50 instances of Product A that won’t be able to have their orders fulfilled. This suggests to me that stock needs to be placed somewhere else and not in a pivot table involving stores.

Please or to participate in this conversation.