2

I have different product types that have different attributes. They cannot be stored in a single table as the attributes are too distinct. There's a couple of options I'm currently looking at: EAV and a table for each type.

My situation is, at the moment, there are only a number of types (lets say 8) but in the near future with almost 100% certainty, this can grow. But the growth is controlled by me, its not defined by users. It will be up to me to grow the product type.

I'm currently inclined to use EAV (for the reason that I can cover the growth easily - I think) but I am not sure as I'm concerned with the performance as well as modeling them in my language of choice (C#). My question is, given the scenario above, is it better for me to create a single table for each product type and add as necessary, or would this be a good case (or not even good, lets say acceptable) to use EAV?

g_b
  • 11,728
  • 9
  • 43
  • 80
  • 1
    Depending on your database of choice, you might be able to store the different attributes of the different products as XML or JSON, which might be a better alternative than the EAV (Which is considered by many to be an anti-pattern). – Zohar Peled Nov 29 '18 at 11:06

3 Answers3

3

There's no short good or bad answer to this concern, because it depends of many things.

  • Do you have a lot of product types ?
  • How do you think each of them will evolve (think to what will happen when you will add new fields to products) ?
  • Do you need to handle "variants" of the products ?
  • Do you intend to add entirely new types of products ?

Etc. EAV is probably a good way to go if you answer if you answer "yes" to some or all these questions.

Regarding C#, I have implemented in the past an EAV data catalog with it, and using Entity Framework over SQL Server (so a RDBMS). It worked nice to me.

But if you need to handle a lot of products, performance can quickly become an issue. You could also look for a "NoSQL" solution, did you think about it ?

Just keep in mind that your model object does not have to match your data model. For example you could perfectly have a stronly typed object for each type of product if you need so.

AFract
  • 8,868
  • 6
  • 48
  • 70
  • 1
    I think noSQL should definitely be considered for such a scenario, especially since their need to be performant on Read will outweigh a knock on write, plus give them the flexibility required for changes in products – zuckerburg Nov 29 '18 at 11:13
  • Right, however if you don't have thousands of products and customers you need absolutely need NoSQL. It also depends of the cost to learn the related technologies, tools, and at which point reliability and consistency of data matters for you. Classical RDBMS keep many advantages on some points. – AFract Nov 29 '18 at 13:12
  • Above I meant "you don't absolutely need NoSQL", sorry – AFract Nov 29 '18 at 16:02
  • 1
    true, it's always a use-case scenario of what you expect and how you see yourself growing as a business when choosing a technology you'd like to use. Pro's / Con's lists always help :) – zuckerburg Nov 30 '18 at 14:51
2

Much depends on the operations that will be performed on entities. If you will:

  • often add new attributes to products;

  • add a lot of products type;

  • implement full product type search (or other "full product type" feature);

I recommend you to use EAV. I have implemented in the past EAV data structure with ADO.NET and MS SQL and don't have any problem with performance.

Also, Morten Bork above recommend use "sub types". But if you want implement some "full product type" features, I think it will be more difficult then use pure EAV model.

Roma Ruzich
  • 692
  • 5
  • 18
1

EAV doesn't really play well with a relational database. So if that is what you are doing. (IE connecting to SQL) Then I would say no. Take the hit in development time, and design a table pr type of product, or make a aggregate table that holds various properties for a product type, and then connect the properties to the relevant tables.

So if a product contains "Cogs" then you have a table with "teethcount", "radius" etc. Another product type has "Scews" with properties "Length", "riling" etc. And if a product type has both cogs and screws, it merely has relation to each of these subtypes.

Morten Bork
  • 1,413
  • 11
  • 23