Does anyone have database recommendations for user generated data structures? I'm currently using postgres and it's feeling a little inadequate. For example, a user might have simple formula like

10 + 5

which I store as a string, and I have a

numberCache

column storing

15

for querying. But a formula can also be a string, or a date, so now I need a

stringCache

column and a

boolCache

column. And then there's user input

if statements

which I haven't thought about yet and may need to calculate on the fly. I have dynamic types, and dynamic relationships, and I'm wrangling postgres into shape, but I can't help but feel there must be something better suited? I was thinking of playing with something more schema-less like mongoDB. Any advice would be appreciated!

What about using a JSON type in postgres? https://www.postgresql.org/docs/10/datatype-json.html

I'd need more information about what you're trying to do. "User generated data structures" doesn't seem like a well-posed category that one can make generalizations about. For example, why would you want to cache the result of

10 + 5

? Repeatedly recomputing it would be almost certainly cheaper than a database lookup. If it is worth caching, the first choice of cache is in memory, which would again be much faster than a database lookup. Do you have millions of users running billions of programs a day? If not, a simple program without a database or caching would likely be sufficient for your purposes.

Yes, you're right, maybe I'm overthinking it and should just compute on the fly. The most complex user inputs might have many dependencies. ie.

v1 = 100

,

v2 = v1+50

,

v3 = v1 + v2

- but I wouldn't expect a chain greater than 20-30 dependencies. It's just important the data is queryable & aggregatable in the future. And I wouldn't be aggregating more than than 10,000 items I find postgres json columns aren't very great for queries that go more then 1 level deep but maybe I can make things as flat as possible. Thanks for your help!

I find the syntax of Postgres JSON really frustrating to work with. I also have mixed feelings about schemaless, but it does seem like a slightly better fit for this kind of a use case, assuming you go with something that lets you do your queries and aggregations in the DB rather than the client.

@Ryan King I'm still curious to hear more about an app where the database has code expressions.

I'm building an interactive gantt chart tool. A quick 2 min overview here

For building an interactive gantt chart tool, my Beads language would offer a RAM database that has number, string, image, sound, records, pointers, etc., and will greatly simplify the tricky issue of resizing your interface to fit different screen resolutions your software will be run on. Desktop resolutions are around 100 dpi, but mobile can reach 500, and any drawing in pixels will not work well, nor will calculating sizes in points work in all cases, as the physical screen sizes vary quite a bit as well. In a Gantt chart program, the majority of the code will be related to drawing, not calculating dates. But you might be happier using an OpenGL foundation because you may want to zoom smoothly in real time, and nothing zooms more beautifully than 3D rendered stuff which of course can just move the camera to zoom without rebuilding anything.

@Edward de Jong / Beads Project thanks but I have no issues with drawing for the time being. Hopefully I can test out beads when I have a spare moment though :)

You might consider a graph database for storing the (ahem) graph?

I've been using BaseX recently and I was pleasantly surprised how nice XQuery 3.1 is to use. Since you are talking about storing expressions XML may be perfect for that. Who knows... you may even enjoy transforming your AST with XSLT 3.1 (although you don't need XSLT). https://docs.basex.org/wiki/Main_Page

@Ryan King have you seen Materialize? https://materialize.io/ if you could represent your computation as a SQL query it would do what you want very efficiently and typed all the way through

@Jack Rusher I was thinking a graph database but all the relationships are stored in the formula string as well. Perhaps I could split the formula into a ast and store that but that feels a little absurd. @Emmanuel Oga Thanks, will take a look. @Harry Brundage Interesting approach. Will look into it!

Ah, I think I misunderstood the situation! It seemed to me that you might have multiple pieces of code in different places that referenced one another, in which case you'd want some link between a node in one AST to a node in another AST to keep track of dependencies between "code blocks".

If you insist on an external database then Neo4j is a lot of fun the two-way relationships are very useful when you’re crawling your graph all different directions. There in my design spec but I actually haven’t needed to use them yet. It’s a very powerful data structure and illuminates most and use cases that would otherwise call for a relational database

Yes, that could definitely be beneficial, thanks! I'll have to develop better understanding of what jobs are required from the database and client to reach the best solution (I also need the graph on the client). I'm thinking mongo might be the simplest solution for the time being - it's easy to learn and will remove the constraints I'm experiencing with postgres. And once the app is further developed I can reassess if a graph database will be more beneficial, I think it's too early to know right now. Thanks for all your advice, it's been really helpful :)

Also when you are asking about Database is you kind a need to specify what language your programming in because not every database has bindings for every language, and also some languages have concepts and data types which are not storable in many databases. It becomes a gigantic pain in the ass when you are having to encode and decode constantly when moving to and from the database. I would call this in impedance mismatch, and it’s one of the reasons I put the database inside the language in Beads because I have data types that don’t exist and other systems such as extended arithmetic, and a bookean that has four states.

For those following along, I think I'll be going with ArangoDB, it's both a document and graph database. I had no idea how terrible Mongo is at querying even basic relationships and I find Neo4j's Cypher query language a bit of a learning curve for me. Arango's query language is a lot closer to sql but instead of joins it uses graphdb-like queries - I actually find it easier to use than sql. On top of that, I can also add micro-services directly on the database server which might be useful in the future if I decide to process formulas on the db. I think it's a nice mix of everyone's suggestions, and thanks again for your help!