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
220 columns in one table? Yeah, you definitely need to do some research and learn how to design a schema that isn't based on incredibly wide tables.
If you had to ball park it, what’s the max number of columns you would use per table?
Right now I’m considering splitting the table into 4. 3 tables per person/job and 1 for contacts.
Or, within each job there are 5ish main topics. So I could have 2 tables. One table for contacts and one table 15 columns wide but I’d store JSON in the cells. The data between each row isn’t related to another row except by contacts.
Is one option more correct than the other?
In a perfect world, I'd say 20 columns per table max, but shoot under that if possible. This isn't always feasible, and I've definitely had some fat tables in some legacy apps I've worked on. But 220 is just unmanageable, especially if you're doing a select * against that table ever in your app.
Thank you for the info, I appreciate it