Topic: Hundreds of columns, index would be required on all of the
columns.
Hi!
How could this be implemented?
There are several items (or rows).
One item (or row) has e.g. even 900 columns.
An index would be needed on every column.
Would it be better to have 900 columns or should the table structure
be something else?
900 columns, table structure:
Item ID, item name, column 1, column 2, column 3, column 4, ...,
column 888, column 889, column 900
-> Index on all of the columns.
SQL clauses are very long with that kind of a table structure.
All of the columns could also be stored to one field too, but then
index would be hard or impossible? to be on all those "columns".
All columns in one field, table structure:
Item ID, item name, all columns in one field
-> How to have index on all of the columns in this case?
All of the 900 columns could be on separate rows too.
Then creation of an index might be easier. But size of the database
might increase.
I mean this, if all columns would be on separate rows. There would be
needed two tables, and two sample items to give an example.
Table 1:
Item ID, Item name
511, itemname511
512, itemname512
Table 2:
Item ID, column ID, value
511, 1, valueForColumn_1_forItem511
511, 2, valueForColumn_2_forItem511
511, 3, valueForColumn_3_forItem511
....
511, 899, valueForColumn_899_forItem511
511, 900, valueForColumn_900_forItem511
512, 1, valueForColumn_1_forItem512
512, 1, valueForColumn_1_forItem512
512, 2, valueForColumn_2_forItem512
512, 3, valueForColumn_3_forItem512
....
512, 899, valueForColumn_899_forItem512
512, 900, valueForColumn_900_forItem512
-> E.g. only one index would be created somehow on table 2 to be able
to make queries based on the values.
Maybe some extra columns would even be added/needed, I do not know, to
be able to make queries based on the values. How would the index would
be needed to be created? Would some extra column be needed etc.?
Some of the items can have different amount of columns. All do not
have 900 columns. So, the amount of columns varies.
Size of Item ID can be big -> It increases the size of the DB with the
latest table structures.
There can be a lot of Items. Maybe millions or tens of millions (or
more?).
How could this problem be solved?
What different possibilities there are in different DBMSs?
Also compression of data would be good. Some DBMSs have data
compression: compression of indexes and/or tables. Compression matters
at least a little too.
|
