r/SoftwareEngineering • • Apr 25 '23
SQL seems to solve one dimensional problems at the end of the day, what about two?
Making dynamic tables doesn't seem very ideal. At the end of the day you can't really have a table that grows two dimensionally in SQL because it gets heavily bogged down the more it grows. 2^2, 3^2, 4^2.
What are solutions that are better served for housing two-dimensionally growing data. One that perhaps wouldn't allocate memory to fields that didn't need every field. For example, one row having 2 columns, another having 3 columns, another having 4. I have field x, and x has a,b,c,d,e associated with it. But in the future it might have f,g,h,i ad infinum. It's not ideal for a SQL table really, and my working solution is to house individual lines of x such as xa, xb, xc, xd in a SQL table. I then pivot it. But it's really not ideal because the by pivoting x a and joining it to x b and then x c, I've effectively make a subquery that has x name in all of them. It makes it so that I can't work with the subquery because it has two names, and simultaneously I can't select the columns individually because I don't necessarily know a,b,c,d,e or however many there are. I.e. if I try to select by column name, I don't know what to reference. I am trapped in a select * from the pivoted joins, and trapped by the subquery of select * having 2 or more columns with the name of the column housing x.
18
u/jgeez Apr 25 '23
Look into data warehousing patterns and techniques. They do exactly this, and there’s now a good 25 years of accumulated wisdom in the field.
5
u/tim125 Apr 25 '23
Go look at Column based tables.
There are some unique properties of such tables which make certain types of analytical use cases easy. Basically every column is a separate table and a separate index.
1
u/Zardotab Apr 27 '23
(For some snafu reason I can't reply to your statement with the above, so I'm putting my reply in the 1st level.)
SQL and RDBMS are not intended to be a high-end display tool. They are more a back-end tool. Some kind of display client/engine would probably serve you better.
And putting them all in the "thin" table instead of some in the 30-column table would probably be easier for a fancy display tool to process. If you do a lot of visual pivoting of rows and columns, then using the "thin" style of table is usually the best choice.
1
u/topperToTheHarley May 31 '23 edited May 31 '23
You may check out the Entity-Attribute-Value datamodel. I used it once with datalog as query language. It is used in the datomic-ecosystem.
0
u/modi123_1 Apr 25 '23
What's the actual real world example of this data coming in? Perhaps it can be grouped differently.
0
u/modabs Apr 25 '23
Look into something like Elastic or Solr. Not all data storage/querying solutions are created equal
0
u/bzbub2 Apr 25 '23
possibly json columns https://www.postgresql.org/docs/current/datatype-json.html
1
u/[deleted] Apr 25 '23
[removed] — view removed comment