0

I need to design database schema where number of columns and type can can vary depending on some attribute(say category). So basically I have one super class which will have some common attributes and sub class(for each category) will extend this class. After some research I have some solutions and want some suggestion which will be better?

  • Use NoSQL and create one table with all the attributes(of all category)
  • Use EAV model
  • Use Json data type in MySQL 5.7
  • Create table for each subclass(category)

Requirements: All the data will come from user input/ forms. There will be around ~15K entry per day and number of categories will increase with time but not so much. I need to fetch some data (of a particular time range) related to each category and then show it one common dashboard. So If I use last approach then I need to join on all category tables and this will slow the process. I have read about using Json type in MySQL and till now I didn't get such down side of this method. With NoSQL keeping all fields in one class doesn't seem good idea to me because some attributes will be totally unrelated and in EAV model some simple queries will become more complicated.

I know this is general scenario but I really don't know with which approach I should go? Let me know if any more information is needed.

Community
  • 1
  • 1
bhanu
  • 89
  • 1
  • 4
  • 2
    I would go EAV, no json, and leverage non-EAV as much as possible to make up for the slow EAV performance – Drew Oct 25 '16 at 05:41
  • @Drew yeah that's what I think at first but after getting to know about json data, now I am thinking that will be better solution. what's your take on that? – bhanu Oct 25 '16 at 05:49
  • I have not spent time on that but I should give it a whirl with 5.7 . At least for performance comparisons to show others. My gut feel on json is that, despite the json schema window dressing in 5.7, that people will abuse it somehow like arrays and CSV, and that it will not perform well. That said, I have not spent time on it to see how the json stream gets converted into indexed data. My gut feel is that json cannot compete with well laid out tables including junction tables. – Drew Oct 25 '16 at 05:53
  • Another option for you is strait mysql no-EAV, and tables using Conditional Joins like [here](http://stackoverflow.com/questions/1255492). The left joins will make your result uber-wide but it may be worth it if the table count is low. – Drew Oct 25 '16 at 06:02
  • Last method, table for each subclass, is essential the same as Conditional Joins method. As of requirements I need to show data on a common dashboard of all category and that will lead to taking join with each type table and slow the process drastically, won't it? – bhanu Oct 25 '16 at 06:11
  • I would expect the worst and index well and maybe get a surprise to the upside. But at least you have real datatypes unlike EAV – Drew Oct 25 '16 at 06:16
  • This question may have an answer here: http://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860 – Walter Mitty Oct 25 '16 at 10:18
  • Multiple tables is probably the worst solution. – Rick James Oct 25 '16 at 18:24
  • Read the discussions with tag "entity-attribute-value". – Rick James Oct 25 '16 at 18:25

0 Answers0