Structure for Products table to store products details from various fields - development

Structure for Products table to store products details from various fields

I’m building an application which requires supporting various categories of fields of products (as in products in Fashion, Electronics, Automobile, Grocery etc.), so obviously, for products in every field (& even for their sub-categories), product properties would vary from category to category. Example: Products in Fashion field, are like Jeans, Shirts, etc. which have properties like size, color, type, material-type, etc., whereas Products in Electronics, can be like Mobile phones, Laptops, etc. which can have product properties like, processor, memory, storage, size, color, etc.

My Question is in regarding, how do I store such information in most generalized & efficient way possible?

What I’ve thought of a solution:

I’ve thought of creating three tables for maintaining this information.

1. products table: This table will contain all products & their categories in nested set form such that each parent would act as a category and each leaf would act as a product. Along with this, this table would contain meta information about the product (like name, purchasing price, selling price, sku, etc. which would be common to any kind of product). Probable Table Structure of this table is as under:

   id(PK), client_fk(FK to clients), parent_id(for nested-sets), lft(for nested-sets), rgt(for nested-sets), depth(for nested-sets), name(meta), sku(meta), purchasing_price(meta), selling_price(meta), created_at, updated_at

2. Other two tables will kinda work like EAV model structure. products_propstable: This table will store all possible properties to any product (from any category) like size, color, processor, memory, storage, etc. with their meta details like description, input-type-details, etc. Table Structure for this table can be

   id(PK), name, label, description, input_type, input_details, created_at, updated_at

3. products_props_data table: This table will contain values for all the properties that product have. Table Structure for this table can be:

   id(PK), product_prop_fk(FK to products_props), product_fk(FK to products), value, created_at, updated_at

Reads on this information would be much more than inserts, updates or deletes.

Questions:

1. Am I following the correct way?
2. Is it efficient?
3. Is there another way around?
4. Advises/suggestions to achieve what I am trying to.

from Laravel Questions and Answers https://laravelquestions.com/php/structure-for-products-table-to-store-products-details-from-various-fields/
via Lzo Media