0

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.

0

One thing that I have seen is a Feature Table. It only consisted of the Primary Key, ObjectID, FeatureType, and FeatureValue fields. This database dealt with people, so the features that would go into FeatureType would be things like 'Date of Birth', 'Place of birth', 'hair color'. The FeatureValue field would get the actual information. Then it was up to the application's forms and reporting to handle everything related to this. It made for some funny queries when we were looking for some specific things, but it made things a lot easier in the sense that when there was a new person feature added, it was done in the application rather than creating a new table or altering the existing ones.

The database generally didn't care if you wanted to create a new person feature, like 'Tattoos.' Just add it into the application code and have it insert into those fields. It even handled when the customer wanted to add a feature to a group of people. He was able to pick a number of people from a combo box and assign whatever he was putting in the form and it inserted a record for each one.

  • This approach is well known as EAV - entity-attribute-value. The good starting point is here: mysql.rjweb.org/doc.php/eav – Kondybas Sep 3 at 12:33
  • @Kondybas I really wish I had that page four years ago when I was trying to wrap my head around the whole concept. That database that I saw it in was so wild-west that I just assumed it was another fever-dream by developer that just happened to be useful. Thank you for showing me to a more structured approach to this instead of what I had going on in my head. – John Herbert Sep 3 at 13:08
  • The only problem I see with this approach is that the table will need to have many fields that do not apply for a given category. As an example in my post, the table should have color, country of origin, allergens and expire date, even though only two of them will be used at the same time. – Adrian Sep 4 at 21:40
  • Actually the table would only have three fields; an ID field for the product (ex: apples), a field for the type of feature (color), and then a description field (red). Adding more features would mean more records in the table. (Add Country of origin to the FeatureType field, then USA goes in the Description field) The select query would end up focusing more on a combination of the objectID and the FeatureTypes. Indexing on it sucked though, since the only field being indexed was the objectID field, and the others were all VarChars. A good thing was that there were never any NULLS. – John Herbert Sep 5 at 11:44
  • I was actually referring to the link that @Kondybas provided. According to that link, the best approach is to have as many columns as my needed attributes would be, plus an extra BLOB field where to store the rest of the attributes in a JSON format. Either way, it looks like I will need to control it from the frontend of the application, so I might as well use your solution since it only uses 3 columns. – Adrian Sep 5 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.