Saving and working with dynamic properties

Question

So, I'm working on an e-commerce application, and my client wants to create categories and products ... obviously. Now, say, a customer will have about 100 categories and 20,000 products.

The client needs the ability to create category properties that make sense for filtering .... so in the category of hard drives there may be such properties as:

• Capacity
• Revs
• Form factor

While the category Projectors may have properties such as:

• Brightness
• Contrast
• Native permission

My question is how to solve the problem of resolving dynamic properties of custom properties, but the ability to use these properties to search, filter and send reports?

Creating a separate table and object for each category is impossible, since I have no idea which categories they will create (i.e. HardDriveProperties, ProjectorProperties).

I thought that maybe I could create an extra column in the database and serialize the custom properties as JSON, but that would still require me to create a specific property object for each category to deserialize the JSON or the general ProductProperties list .... which, I think it can be quite expensive to work.

How do others solve this problem?

Answer 1

The attribute-attribute model (the so-called open schema) is one way to solve this problem.

The essence of the template is that you create columns in rows. Instead of HardDrive (and Projector ) tables, which look like this:

 HardDrive(HardDriveID, Capacity, RPMs, FormFactor) ------------------ 1 1TB 7200 External 

You have the Category , CategoryProperties and CategoryPropertyValues tables:

 Category(CategoryID, Description) -------- 1 Hard Drive 2 Projector CategoryProperties(CategoryPropertyID, CategoryID, Description) ------------------ 1 1 Capacity 2 1 RPMs 3 1 FormFactor 4 2 Brightness 5 2 Contrast Ratio ... etc. CategoryPropertyValues(ItemID, CategoryPropertyID, PropertyValue) ------------------ 1 1 1TB 1 2 7200 1 3 External 

Answer 2

Create a table that contains the ProductID column. Create a ProductAttribute table that has the following columns: Product (ProductID link), Name, Value.

One site that I saw had several Value columns, one text and one number to provide efficient range searching for numerical values.

Answer 3

Assuming you are using a relational database, I would probably use something like rows:

 Category(Id, Name) Property(Id, CategoryId, Description) Product(Id, CategoryId, Name) ProductsProperties(Id, ProductId, CategoryId, Value) 

Thus, you can determine which properties should be available when adding / editing a product belonging to a certain category, and the product may have inifinite properties.

This would be better than just serializing JSON values, because searching / filtering for serialized values ​​can be very painful. However, this approach uses a relatively normalized database structure and therefore can be slow for full-text search if you have many products, properties, ...

Answer 4

I think your last paragraph will be the route I would send. My main concern would be to do (de) serialize a large number of records.