To preface, I’m currently rewriting a personal webapp to use MySQL instead of storing everything in hundreds of JSON files. I’m currently in the testing phase of generating tables with the data from the JSON files, destroying the tables, adding more columns and data, repeat, all to make sure everything is working as intended.
My issue is that occasionally I’ll create too many columns and then I get an error saying something about the row being too large? I’ve also noticed that if I change the parameters of what data is allowed to go in the column, I can generate more columns. I know there is some relationship between number of columns, the data that can go in a column, data size, and row size but I don’t know what’s going on. I’d appreciate it if someone could broadly go over how row length(?) can affect number of columns.
Thank you
Just spitballing here, but it sounds more like you need a nosql database instead of a relational one. Those are designed to ingest store and search things like json objects.
I’ve played around with MongoDB before. Do you know if nosql can still have relationships? Meaning, about the only data that will be reused per row would be contact info. In my app’s current state with everything in JSON files, I have the same contacts rewritten 10s of times. When contact info changes which happens often enough, I have to search through all the files and update the contacts in each place. I really want there to be 1 contact table that can be referenced from.
You would create a contacts object, with a unique reference id number.
Then all your other records would point to that unique reference id number, and would not hold any contacts data.
Changes to the customer would be done in the contacts object.
Queries could be done that join both groups of data.