#Need help on how to model product data

47 messages · Page 1 of 1 (latest)

kind skiff
#

I have products table and product_types table. A product belongsTo a ProductType.

product types such as 'Simple Product', 'Bookable Product', 'Product Bundle' etc

The products table has the usual columns such as name, price, desc etc but i need to store additional data (settings) about a product depending on its product type, but not all products need to store settings. These are not custom fields, in other words the settings are not dynamic, the settings are concrete according to product type and columns need to be created for them. They are also not product variations.
One approach, have a single table for products, with columns for all the 'settings' that might be needed in each different kind of product. You use whichever columns are appropriate to each kind of product, and leave the rest null. with this approach I cant enforce integrity and if there's many product types with many settings the table could get unwieldy.

I was thinking polymorphic, where products table has productable_id and productable_type (which points to their respective settings table), but since not all product types need to store settings this isn't viable. I wouldn't need to store settings for a Simple Product, but I would for a Bookable product as I need user to enter data such as Capacity, when creating a Bookable Product.
does anyone have a good solution?

slim haven
#

Are these product types dynamically generated in your application? Or are they something that you define in the codebase?

#

I.e. do you define X number of product types or do you define X number of settings and your app users can make as many product types with those settings as they want?

kind skiff
slim haven
#

Do you have logic built around the extra columns that each product type needs to have or is it just metadata?

kind skiff
#

so far in my research it looks like i need to implement class inheritance https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860

Stack Overflow
How to design a product table for many kinds of product where each ...

I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:
Support many kinds of products (TV, Phone, PC, ...). Each kind of p...

#

yes there would be logic, and when querying for products i need to access the settings so and fast so EAV is out.

slim haven
#

Do products ever change types?

kind skiff
#

no

#

you would need to trash the current product and create a new one on a different type

slim haven
#

so two options then. (both have downsides)

  1. Create a unique table and model for each product type
    pros: All logic related to each type is completely separate making your code clean
    costs: some duplicate columns, no Product::all() queries

  2. create a products table and a table for each product type using a one to one polymorphic relationship
    pros: Product::all() type queries available, no duplicate columns
    costs: everywhere in your codebase it becomes necessary to fork your logic based on what product type the product is. instead of designing completely separate interfaces & logic for each type you end up having to utilize a bunch of switch($product->type) statements

#

Personally I'd choose option 1 since it is easier for the developer in the long run. Especially if you add new product types. You just create a new model and table with new interfaces instead of having to refactor all the existing ones to support the new type

kind skiff
#

with option 1 though, it would be a pain when it comes to querying data, managing cart items, and order line items as they would all point to different product type tables. Id rather have a parent products table and branch out for those products that need settings.

slim haven
#

Not really. You just utilize polymorphic relationships from the cart to the product types in it

kind skiff
#

for point 2. I see your point on forking your logic based on what product type the product is but i need to do this anyway as pre and post checkout specific events needs to take place if user adds a bookable product to cart.

kind skiff
slim haven
#

Yeah lets say you have an Order model and you want to get the products in the order you'd just have a polymorphic many to many

kind skiff
#

i would have to duplicate a lot of columns though on option 1

slim haven
#

Theoretically yeah. You could flip the relationship on its head though to reduce that

ProductType1 belongsTo ProductDescription (ProductCommonColumns)
ProductType2 belongsTo ProductDescription (ProductCommonColumns)

kind skiff
#

back to option 2, is this feasible if Product Simple doesn't require a settings table like a said in OP?

slim haven
#

Then you still have all the logic split out with multiple type tables but the common data can be stored in one place

kind skiff
#

right

slim haven
#

kinda redundant but it works

kind skiff
#

so for option 2: products table (id, price, sale_price, description (any common columns would go here) productable_id, productable_type) and then settings table(s) for each productable_type

#

so ProductSimple table would really just have one column id, i guess it does allow for future growth on Product Simple should it need settings in the future

slim haven
#

Yeah and with that setup you actually reduce the costs of option #1 since you can then do a reverse lookup and get all products of every type via their ProductDescriptions

kind skiff
#

the issue i dont like about option 1, is i would have to do many queries to get all products, it would limit filterting across product types too, i couldnt filter by price.

slim haven
#

You could do something like

ProductDescription::with('product')->where('price', 1)->get()
kind skiff
slim haven
#

I guess in a roundabout way both options have collided. So you could do something like this:
products table

  • common columns
  • product_type_id
  • product_type_type

product_type_1 table
product_type_2 table etc

Then you get the global lookup of Product::all()

but if you are viewing a single product you look it up by product type.

e.g. your routes would be something like:

/products - index
/products/type1/{id} - view
/products/type2/{id} - view

Then you can still utilize separate controllers, views, etc per type but on the index pages where you want all of them you have a way to get that

#

You can also still do things like Order polymorphically belonging to many product_types if it makes more sense to do it that way with the logic tied to each type

kind skiff
#

right, thanks for taking the time to hash this out with me.

slim haven
#

No problem! I've never had to solve this particular problem before but now I think I have a good way to do it in the future

kind skiff
#

i think im going to go with polymorphic way, its clean and ensures integrity

#

the Class Table Inheritance pattern is too advanced for me

slim haven
#

Yeah its safer too than having to rely on an atypical database design pattern

kind skiff
#

after chatting would you still go option 1 for yourself?

slim haven
#

I'd go with the "combined" option that merges the best of both worlds now

kind skiff
#

this one?

#

products table

  • common columns
  • product_type_id
  • product_type_type

product_type_1 table
product_type_2 table etc

slim haven
#

yeah

kind skiff
#

yeah thats what i'm going to do, thats still the polymorphic way though, because of cols product_type_id, product_type_type or am i missing something?

slim haven
#

Yeah its polymorphic

kind skiff
#

again thanks for all your help 🙂

#

gonna implement now