I'm just looking for some advice on database design.
I have several types of products, some being internal and some that come from external sources/api, but I still store certain information for those products.
At the moment I have a separate table for my internal products and a separate table for products relating to each source/supplier.
When a customer requests a price for a range of products I query each of these tables prior to generating a price to present the quote. So when the information is presented to the frontend I end up with a collection of quotes like this:
return collect([ 'products' => $this->product->where('status', 'active')->get(), 'externalOne' => $this->externalProductOne->where('status', 'active')->get(), 'externalTwo' => $this->externalProductTwo->where('status', 'active')->get(), ])->all();
Where ideally I would just want this:
return collect([ 'products' => $this->product->where('status', 'active')->get(), ])->all();
As I source more suppliers for product ranges this is quickly becoming a pain in the arse to maintain and update. What I think I want to do now is bring all products into the same table but add in a category/type field which I can still use to determine which suppliers API I call for a price for that particular product range.
Each of the product categories all have different columns/fields of information stored which is why I initially created a table for each of the suppliers as the columns were different to my internal products. But they all share similar columns.
What would be the best way to tackle this? Should I create a table for each of the suppliers which just contains the columns related to that product range and then use Eloquent relationships to pull that information when I need it? Or, is there some other way where I could dynamically created fields/columns to store the different information, such as storing in one particular field as json?
If anyone managed to read this far without falling asleep and has some helpful advice for a newbie, I would greatly appreciate it!