0

I have to create a database specification for a products database. The issue is that each category of product (tv, smartphone, etc) has different columns. My first thought was using a noSql database, but I have been asked to investigate doing this in SQL so I read this on wikipedia which suggests that in order to use EAV that is scalable, you need to have a table for each data type.

I am not a database master, so I need some advice. I have come up with this database model:

enter image description here

My thoughts here (if not apparent) is that in the interface, when a new category is created; columns can be defined and then when entering the product from the system, it can look at the products table and each EAV table to get the columns and values.

My question is, how scalable is this? Is it a viable solution and does it have any major disadvantages?

r3plica
  • 13,017
  • 23
  • 128
  • 290
  • Nowadays the usual way to implement dynamic columns is to use a single JSON column rather than the EAV pattern. But this depends highly on which DBMS product you are using, some have better JSON support than others. –  Jun 24 '19 at 10:53
  • I am using MSSQL. Using JSON, would that be as fast as using EAV when querying? – r3plica Jun 24 '19 at 10:55
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. – philipxy Jun 24 '19 at 12:11
  • 1
    [EAV is by nature not scalable.](https://stackoverflow.com/a/23950836/3404097) The wikipedia article contains a lot written by people who clearly do not understand the relational model. PS Time to read a published academic textbook on information modelling, the relational model & DB design. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) PS Please research before considering posting. (Reading a wiki article is not research.) See [ask] & the voting arrow mouseover texts. – philipxy Jun 24 '19 at 12:17
  • Re subtyping/inheritance/polymorphism (for which EAV is frequently misused as a design antipattern): [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) [(Database Design - products attributes): What is better option for product attribute database design?](https://stackoverflow.com/a/2945124/3404097) – philipxy Jun 24 '19 at 12:26
  • Possible duplicate of [(Database Design - products attributes): What is better option for product attribute database design?](https://stackoverflow.com/questions/2945045/database-design-products-attributes-what-is-better-option-for-product-attri) – philipxy Jun 24 '19 at 12:32

0 Answers0