Too many Columns / Fields in Table, ideas on how to resolve?

If you do not every piece of data in every case, you could divide record in several tables and make 1-to-1 relationship between them. And add default eager load to it.

Thanks for the tips, I'll do some research and test it out.

Why not have :

id
user_id

connection_number //your 1, 2, 3 ...
connection_settings1
connection_settings2
connection_settings3

And if a user uses 2 connections you have 2 rows associated to this user ?

You may have maybe different number of settings for each connection, so maybe some fields will remain always null.

Maybe it's not a problem.

Maybe it is, then @martal solution is great.

And you could also do a polymorphic relationship between the users and the many different connections to get a normalized collection of all connections of a user.

What about polymorphic relationship, have you tried this?

https://laravel.com/docs/6.x/eloquent-relationships#polymorphic-relationships

Create a table with each row containing

connection_id
connection_settings1
connection_settings2
connection_settings3

etc

I would take it even one step further than @snapey's suggestion and have these fields in my table

• connection_id
• setting
• setting_type

and use a one to many relationship.

I like the idea the three of you had about putting each connection on a different row, and defining which they belong to with a one to many relationship.

Basically making it infinitely scalable while only using a few columns in a single table.

This would require a complete restructure of my controllers, which I don't think would be too bad. But the worst part being, it would require me to manually move each field of my production database table over to the new table type.

Or require all my users to re-setup the connections on their accounts.

Both could be quite messy. I'll think it over.

It's quite easy to do with a few SQL commands

Something like

insert into sometable (connection_id, setting, type)values (select connection_id, connection_settings1, 1);

And then do the same thing for setting 2 and 3,

My solution thanks to your help:

I ended up using a few of your guys advice. Going with two tables, (connections and connection_settings) and using the "one to many" relationship (hasMany and belongsTo). Using the original "connections" table to house the hasMany relationship, and a few server settings fields.

Then using the second "settings" table to house all of the individual connection types and their independent settings. Reducing it down from over a hundred columns before, to just a few, and then identifying them individually with a connection_type value. (Each one using a different row, instead of a different column.)

I then pass the connections and connection_settings queries from my controller into a Resource Collection, and using a bunch of foreach and isset statements sort through them all, and combined them all into a single collection which exactly replicates my original api json output. (So that I do not have to modify any of my code which consumes the api data.)

I also set it up so that if a user does not need to enable any specific connection types, instead of writing null fields to the table like before, it just doesn't add it to the database at all (so that it does not waste any unneeded rows). Instead using my resource collection to just pass in faux null fields for any connection type that the user doesn't need. (This is just to prevent any front end errors from missing variables not existing at all.)

I do have one question though:

I figured out my "store" function, just cycle through the inputs with a foreach, if isset pass to database.

But what's the best solution to edit/update my settings fields when using a hasMany relationship. For instance each user will now have a single connection with dozens of connection_settings. (one to many)

When a user uses the update function in the dashboard do I just delete all of them and re-write them over again to the database?

Or is there some simpler method that won't require me to query each "connection_type" individually by their value and id and then pass in the updated values? (This sounds like it'd take a lot of lines and queries to accomplish compared to the delete and re-write method. Maybe I'm just overthinking this though? And eloquent has some magical feature to get this done I'm not aware of?)