Level 13
Do you have a unified product schema that would work for any supplier or do you require different fields for different suppliers?
Dec 7, 2017
11
Level 8
Database design advice
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!
Level 8
@36864 there are a handful of fields which all products, regardless of supplier, use. But then they all have different columns for the rest of the information which differ.
Level 13
One way to do it would be to just have a products table which holds common product attributes (name, size, color, etc.), a supplier table, and a many-to-many relationship between them with supplier-dependent attributes (price, probably not much else?).
This doesn't really work if you need to save different fields for different suppliers. I'm not seeing the use case here, but maybe you can provide an example of how the same product differs depending on the supplier.
Level 8
@36864 the products are things like airport parking, airport lounges, airport hotels etc. Each of these types would have basic info but then that's where the similarities end.
For example, parking products do not store prices because these are either fetched from external API or from a price file/table. Lounges, on the other hand, have static prices for adult, child and infant.
Level 2
Not sure if this is possible in MySQL or whatever database engine your using but you could perhaps create a view like this:
create view all_products select cola, colb, colc from internal_products union all select cola, colb, colc from external_products ;
then maybe this would get picked up in eloquent as a view or you could use
$rows = static::selectRaw('cola, colb, colc allProducts')
I hope this makes sense, sorry if not but Laravel is very new to me.
Tim
Level 13
That's quite a bit more complex than I anticipated.
The way I would approach this would be to first define a set of rules for how prices are determined. I'd probably define 3 types of prices: fixed, variable and external. Fixed prices would just be a flat number. For variable prices, I'd have a variable price table that references the product and has two data fields: 'type' and 'price'. For example, the Lounge product would have 3 entries in that table: ('adult', [adult_price]), ('child', [child_price]), ('infant', [infant_price]). External prices would have a url that points to the external webservice that provides the pricing data.
Alternatively, instead of variable pricing, I'd have separate fixed price products for each variant (lounge adult, lounge child, lounge infant).
As for extra attributes, this can also be done with a couple of extra tables: product_extra_attributes and product_extra_attribute_values. The first one holds the names of all extra attributes for each product. For example, ('Lounge', 'Has_Smoking_Area'), ('Lounge', 'Has_VIP_Area'). The second table holds the attribute values for each product. For example, (Lounge_1, 'Has_Smoking_Area', true).
This does add some complexity, but hopefully would be flexible enough that you don't need to keep adding tables for each new type of product.
Level 4
Is your concern for maintenance the updating of columns in the database, or the work to update the code with the various references in the collect array?
Honestly, the way I would handle it is to have the DB do all the work. Depending on your DB platform, you can either use JOIN statements, or create a view that encapsulates all of the different tables, then modify your Model class to use the view.
Creating a view in the DB would be better for cleaner eloquent/Laravel code.
Level 8
@TimMyers1968 that's interesting, I've never thought about creating views in MySQL so I'll take a look in to that.
@36864 That type of design sounds like it could improve things for me. Having a products table and then a separate attributes and attribute value tables could make it easier. If I can solve my current issues of having to create a new product table each time a different product type or supplier is included then that saves me a lot of development time.
@kobear My main concern is that at the moment if I add a new supplier or product that has a different set of attributes then I have increased development time to get all these things implemented. When it comes to maintenance it can get very messy as well.
I would love to only return a collection with a single list of products rather than having is retrieve multiple. This would reduce the complexity of my code somewhat and just generally make it easier to maintain.
I will definitely have a look at creating views with MySQL. It is something I haven't heard of before and sounds very interesting. Not sure how I would modify the Model classes to use the views though.
Level 4
@mjoc1985 Another option is that you can store all of the options in a single column in the database, in JSON format. If you do not need unique querying of the pricing options, but rather only need to retrieve the details that would normally be stored in another database, you could convert it to JSON and store it on a TEXT column in mysql.
That way, you would have one table, with the variable data stored as objects in that field. No muss, no fuss.
1 like
Level 8
@kobear I've just been playing around with the JSON option, which has actually turned out to be the solution that best fits. Now that I've updated MySQL on my server to 5.7 I don't lose out on being able to query my database and any of the attributes stored in the JSON column. I've not reduced my table column count from 39 to just 8!
I've been able to separate the pricing options into a price JSON column, as well as create a JSON column for product features and product settings.
I think I'm going to adopt this approach for the rest of my tables.
1 like
Level 4
@mjoc1985 Glad that worked for you. Good luck.
Please or to participate in this conversation.