0

I have to store in MySQL some kind of data catalog with mixed arrays, e.g.

ITEM_TYPE1 | A         | 7  | long_text | 1000-80   | N | 0 |
ITEM_TYPE1 | X         | 8  | long_text | 9999-80   | B | 1 |
ITEM_TYPE2 | 7.50      | X1 | 1         | long_text | 8 |   |
ITEM_TYPE2 | 1000.85   | 4F | 8         | long_text | 0 |   |
ITEM_TYPE3 | long_text | 9  | 9         |           |   |   |
ITEM_TYPE3 | long_text | 1  | 1         |           |   |   |

As you can see, all ITEM_TYPEs have different number of array values with different type (char, int, bool, text, decimal), so I can't predefine right column types in a single table. Besides, I can't use serialize or json functions, as all data must be sorted and in future selected by filters using MySQL engine.

What is the best (best - I mean less data size on HDD and more productive with SELECT queries) way to store such data arrays? I see two options:

Create predefined columns for data, such as

int1, int2, int3, int4, int5, chr5_1, chr5_2, chr5_3, text1, text2, text3 <...>

but it is less size-effective ...

or create separate tables for each ITEM_TYPE with predefined columns, according to ITEM_TYPE values?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Alexander M.
  • 181
  • 2
  • 10
  • I assume each of those values means something, it would help if you associated some meaning to each of these values rather than just throwing them into anonymous fields. – Nigel Ren Nov 01 '19 at 17:01
  • That just looks like junk - do any of the comma separated values have any discernible meaning. AND don't even think about storing comma separated strings.There's no way that data in that form is useable. – P.Salmon Nov 01 '19 at 17:02
  • "comma separated strings" - that was (now fixed) my representation of values array :) – Alexander M. Nov 01 '19 at 17:11
  • SQL does not have the concept of arrays. – P.Salmon Nov 01 '19 at 17:16
  • I understand this, so I'm asking the best way how to get around this problem... – Alexander M. Nov 01 '19 at 17:18
  • @NigelRen this meaning is stored in another table, which completely represents how to work with the array. In fact, this values are parameters of fixture products (standard, head size, width, length, strength class, etc... ), so my script is able to congregate this array to a human-readable string, e.g. using the first row above, we can get NUT A 7-steel-galvanized DIN 1000-80 N-0 – Alexander M. Nov 01 '19 at 17:26
  • 1
    https://stackoverflow.com/questions/11779252/entity-attribute-value-table-design may be worth a read. – Nigel Ren Nov 01 '19 at 17:29
  • 1
    This is a database modeling exercise. You need to figure out what are your objects, relations and attributes. Then you split these into different tables. An SQL query will merge these together to produce your result table. Showing us the result only, it is very hard, in some cases, to reverse engineer. That would be a meeting at work, to get from the client what he wants, not what he thinks he should get. – Nic3500 Nov 01 '19 at 17:35
  • @Nic3500 I am this client :) So searching for optimal way to store data... I already have portal for internal use with storing data in a single table, but now I'm going public and want to upgrade scheme to avoid performance issues in future – Alexander M. Nov 01 '19 at 17:54
  • 1
    Such a "simple" question opens a can of worms. Database modeling is a speciality itself. The basics are covered in entry level database courses. But it is not well suited for SO format. – Nic3500 Nov 01 '19 at 17:59

0 Answers0