0

I have some items and each one of those has some properties:

item1 (prop1, prop2)
item2 (prop1, prop3, prop4)
item3 (prop2, prop4)

Some filters, group by, and where clause on items by their properties are required. so, what is the best way to store these items for achieving maximum performance:

  1. using a single table with a column called properties that stores properties as JSON or XML format to preventing join between tables,

  2. or having an additional table for storing them (items and properties tables)?

and why?

Thank you.

Pedram
  • 828
  • 9
  • 24
  • Seems like you want a one to many relationship. But asking the "why do this in an RDBMS", isn't what Stack Overflow is for. – Thom A Dec 28 '19 at 18:18
  • Thank you @Larnu, I agree with you, but I'm asking about the performance. in other words, is it a good idea to use a JSON column instead of creating a new table to improve the performance? (according to the operations we need: filter, group by, where...) – Pedram Dec 28 '19 at 19:38
  • 3
    If you going to be searching for the value, then XML/JSON is the wrong choice. – Thom A Dec 28 '19 at 20:11
  • Thanks @Larnu, and could you please let me know if you have any link or reference or querying experimental data in the same scenario and analyze the results in the execution plan? I've already read [**this**](https://stackoverflow.com/questions/43494824/when-can-i-save-json-or-xml-data-in-an-sql-table) post. – Pedram Dec 29 '19 at 05:58
  • I just want you to explain (in detail) **why** you say that. – Pedram Dec 29 '19 at 07:44
  • 1
    I would be inclined to model extra tables. item, property and itemproperties. That way you can pull back items with a property or see all the properties of an item. Keeps things simple. – JGFMK Dec 29 '19 at 12:37
  • Thank you @JGFMK, I completely agree with you, but I have a database with JSON fields, I wonder if changing the model could help me to improve the performance or not and if the answer is yes, how much?? I need some references or experimental results to compare these approaches. – Pedram Dec 29 '19 at 13:38
  • This seems like an ideal time to use your Dev environment to test, @Pedram . – Thom A Dec 29 '19 at 17:04
  • @Larnu I do not want just do trial and error, I prefer to have some valid references before I start doing that. – Pedram Dec 29 '19 at 18:53
  • So you mean you want someone else to do or the testing for you? We don't have access to your data, or set up, so any test we perform be conjecture based on no Information of what your environment is, or it's usage. Testing this yourself, if nothing else, is a great learning experience and you get very real reaults. For the environment you have. – Thom A Dec 29 '19 at 19:11
  • Dear @Larnu I do not want you to do anything for me, I just want to know that which approach is better for acheiving the best performance (for example) recomended in microsoft documents or any other valid references or by someone experimental results, not by our opinion. Anyway, thanks for your attention – Pedram Dec 29 '19 at 19:32
  • It's not an opinion; it's simply how an RDBMS works. I honestly don't understand your reason for not want to test this yourself, but if you don't want to take the time, then others aren't going to do that for you either; it's very much a give and give relationship on SO. You give your research, your attempts, explain what isn't working, and we give you reason why it isn't working, and how to fix it. Give us some data, some examples, etc we can do that. It's not give and take (you take the answers we give). You have my (expert) advice, if you wish you can ignore that. Good luck either way. :) – Thom A Dec 29 '19 at 19:54
  • RDBMS such as sql server 2019 can work properly with JSON data now. I've already started to change the models and I converted json data to some tables, I will share my results here. But my question is not dependent to this situation, it is general. – Pedram Dec 29 '19 at 20:07
  • And of course I appreciate your help @Larnu ! – Pedram Dec 29 '19 at 20:08

0 Answers0