I'm building an inventory system focused on sound companies in Laravel 7. So far I have 3 models: Product, Variant, and Stock.
A product can take two paths:
product --> [hasMany] stocks
product --> [hasMany] variants --> [hasMany] stocks
As you can see, both paths end up having stocks.
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