1 week ago

How to model my data efficiently

Posted 1 week ago by jginorio

Project Specifications:

I'm building an inventory system focused on sound companies in Laravel 7. So far I have 3 models: Product, Variant, and Stock.

  • The product model is for creating the product itself.
  • The variant model is because the same product, lets say a cable can have many lengths.
  • The stock model is where the barcodes of each product are stored and you know the quantity of each product you have.

A product can take two paths:

product --> [hasMany] stocks


product --> [hasMany] variants --> [hasMany] stocks

As you can see, both paths end up having stocks.

The problem:

I want to be able to show the available quantity that each product has. If the product has variants, I want to be able to show the available quantity per variant.

What is the most efficient way to model this data? Should I use polymorphic relationships on the Stock Model? Should I create two stocks table (1 table for the Variant and 1 table for the Product)? Or should I use a hasManyThrough relationship with Variant and Stock?

What I've tried:

I created a polymorphic relationship for the Stock model. So that both Product and Variant models can have Stock. But I don't think this is the most efficient way.

Here is my database design https://drawsql.app/wsp/diagrams/wsp/embed

Please sign in or create an account to participate in this conversation.