Re: Implementation Advice: Categories and Product Specifications

[James Schneider]

On Oct 2, 2017 11:07 AM, "Paul" <sevenr...@gmail.com> wrote:

I have Product and Categories. A product can be in multiple categories and off course a Category have multiple products.

Many to many relationship,  likely with a custom 'through' model so you can attach other fields to the intermediary model. 

The Categories can have Subcategories’ which can have Subcategories. I used a Category Model with a circular Foreign Key.

I'd recommend tracking a category hierarchy using an MPTT model a la django-mptt or django-treebeard.

Each Product can have specifications/attributes based in which Categories is.

An attribute should be a separate model tied to one or more categories in this case. 

So the specifications are of the products but depends on the Categories.

I'm assuming that a product will have other attributes in addition to those added by the categories. Those should tie back to the aforementioned attribute model. 

 Your product model should define a method that pulls attributes from both categories and the product itself to return a single list,  or perhaps a tuple containing a list/dict of category attributes and a list/dict of product specific attributes,  if you need them separate. 

I can’t add the specifications as Products attributes because they depend on categories, and the user can change categories.

Then don't. 

I can’t add them as attributes per Category because the value are/can be different for each product.

Then you'll need an intermediary table (M2M) between Product, Category, and Attribute with one or more additional value columns. 

Also an attribute/specification can appear in multiple Categories.

Doesn't matter if everything is related in the DB correctly. 

I want also to search for a Product filtering by attributes.

See the big M2M table I mentioned above. 

I think I need a separate Attribute Model that is linked (foreign key) to both Product and Category.

Yes. 

Also a way of “protection” in case a Product change categories, a Product and/or a Category is deleted.

What is the optimal approach as flexibility and performance ?

The crux of your issue boils down to the design of the table that ties together the product, categories, and attributes a b7 the associated values for those attributes. Your validation logic is probably going to be insanely complicated unless your attributes can only contain certain values for all products. If some attribute values are only available based on the presence/value of other attributes,  your validation logic will quickly grow out of control without a good design and detailed definition of your business logic,  and even then...

-James