I am trying to come up with a simple way of implementing the following design, but I always end up with something overly complicated.
I am trying to design a database where companies can store products. Each product belongs to exactly one category, and depending on the category, they share some common attributes that I want to be mandatory. They can also have different attributes that are up to the company to define.
As of now, and based on this question I asked last year, my idea is to have the following:
- Company table to store everything related to the company (one company can have several products)
- Product table to store everything related to the product (one product can be in one category, and can have several features)
- Category table just to save the name of the category
- Attribute table attributes that can be defined by the company for that product
- Product Feature table joint table
With these tables, companies will be able to add their products to a category and add as many attributes as they want.
Now, dependant on the category, I want to make mandatory to define a set of attributes. For example, if we are talking about groceries, and a category would be 'Fruits', I might want the company to always provide the following attributes: color, country of origin. If instead of fruits, the category is 'Cereals' I might want to be always provided with the following: allergens, expire date...
I have thought of two different solutions, which are:
- Create a new table for each category that needs these mandatory attributes. This, of course, will be a problem as if a new mandatory attribute is added to an existing category, all previous ones will have no value. Also, every time I add a new category I should define a new table for the attributes.
- The other option I have thought of is to simply manage this in the frontend of the application. Once a company adds a product belonging to one of those categories, prompt them to input the mandatory attributes and simply save them as a 'normal' attribute.
I am more inclined towards using the second solution. Using the first one I could end up with a ddbb with dozens of tables, but I would like some input to see if anyone can come up with a better solution.