Source
r/webdev•
DragoonDM

Storing a large number of user settings

I'm working on a web app, and I'm in the process of adding a user authentication system (probably just going to go with FB/Google auth) so that users can save settings (and possibly progress) with the app. The problem is that there are going to be upwards of 150+ different user settings. I'm planning to use a MySQL database to store user data, and I've heard plenty of times that you never ever store multiple data items in one column, so using a 150+ column table really doesn't seem like the best way to go about doing this. Would two tables, one for basic user info and another for generic "settings" items (name, value, user_id [foreign key]) be a good way to handle this? Or is there a better approach for storing/retrieving large amounts of user data?

The project in question is a web based hiragana/katakana flash card app, and I'm planning to allow users to choose which kana are displayed (along with several other settings, mostly boolean).

Thanks!

Posted Jun 12, 2014, 9:57 PM UTC

114
tazaar•

Bools can be compressed with bitmasking, I'd best let wikipedia explain, At a previous company we had a ton of bools that we saved as bytes in DB and used them as a bit array in the program. However I'm no DBA and your performance may change, try before you buy :) BTW you state you don't want 150+ columns, do you want 150+ rows per user then? ;) I think something like table users(..., bool_settings BINARY(255)) would suffice.

2
DragoonDMOP•

do you want 150+ rows per user then?

Well, ideally not, but that seemed like the better of the two options. I like the bit array idea, though. I think I might go with that (pending some more research). Thanks!

1
kisuka•

I'd say maybe do something like 2 tables:

user_settings

  • user_id
  • setting_id
  • enabled

settings

  • id
  • setting_name

settings table would have all your possible options which you could display on a page with an enable / disable option. By default no record found for user in user_settings would be enabled. Once they set an option it would do a insertOrUpdate (create new record if user doesnt have that setting in user_settings, update record if that setting id is found for user). enabled field would be a tinyint with 0 for disabled and 1 for enabled.

At least, that's how I'd do it if I was in a rush and lazy.

Or maybe even have it delete the record from user_settings if changed from enabled to disabled. user_settings could be user_disabled_kana.

2
DragoonDMOP•

At least, that's how I'd do it if I was in a rush and lazy.

I'm resisting the urge to take quick and dirty shortcuts with this project, since I'd like to have something portfolio-worthy.

2
html6dev•

But you are looking at bitpacking as a viable option? To put it simply, the horrors of having to do that sort of thing is one of the main reasons we have things like relational databases. You shouldn't back yourself into some proprietary data format like that from the get go. What if you end up needing more settings in the future, this changes the lookup mechanism. What if you decide you want different settings for different auth levels? A flat bit based data format means youre completely boned. What if you want more clients/endpoints to consume this data in the future? Do you really want bitwise operations all over your code base for something as simple as storing user settings?

The way I see it you are probably looking at this from the wrong perspective. Yes they are all user settings but is it really that flat are do they group up into logical domains (as all data tends to do)? Odds are you need to find those groupings and each of those is its own table that joins to your users.

Finally, you might not even think twice about 150 properties in a json object you are throwing directly into a nosql db. That is also a possibility.

2
DragoonDMOP•

What if you end up needing more settings in the future

That's one of the issues I was planning to look further into. I do want a scalable option.

Using separate tables for distinct sets of settings might be a good idea, and I'll definitely look into nosql/json options. Thanks.

1
html6dev•

No problem. I brought it up especially because if you want this as a portfolio piece...if a candidate linked me their github and they were bit twiddling to store data like Id definitely think "wtf?" If it was for web development. Maybe others would disagree but I definitely think a high level of proficiency with sql is an absolutely core skill in this racket and you want to show that off.

2
tazaar•

Is a bit a proprietary data format now a days? Should have copyrighted that when i could. Regarding expansion, BINARY with 255 bytes holds 2040 bits, if you need more than 2040 bools, microsoft sql supports 8000 bytes = 64000 bits, If you need more than that for boolean user settings you are probably doing something very wrong. Considering at the end of the day everything is still bits i find your arguments funny. Also can't remember which sql engine but it automatically compresses bit-sized columns in bytes. And remember 1 bool = 1 byte, 8 compressed bools = 1 byte. If more endpoints wants data you replicate the db and use caching, dont confuse the issue. Ideally you query one table every login, bitmasking is way more efficient than repeatedly querying a db for booleans. Bitwise operation is as simple as it gets, you have 4 operator NOT, AND, OR, XOR. They never change API or structure, does it get any easier?

1
html6dev•

Yes it's proprietary it belongs to you as in you are the only one who understands the format without documentation. Other than that Ive done my share of bitmaskimg in embedded systems but I'm not going to have a conversation about it being a good idea in modern Web Development. You can create sockets in assembly language too but I shy away from it (unless system constraints dictate something else). If you think this is a good idea for something used as a portfolio piece for a Web developer... Well hopefully hiring isn't part of your current position.

Edit: oh and I said endpoint to consume the data...

1
[deleted]•

[deleted]

1
[deleted]•

[deleted]

1
[deleted]•

[deleted]

1