Best way to store user preferences for a website in MySQL - BINARY or INT

Question

I know this question has been asked before and the best answers seems to be the following: Best approach to save user preferences?

However I have some additional criteria which is why I am asking this question again, I need to be able to compare 1 user's preferences to another user's preferences and quickly get the differences between the two users All user preferences will be a boolean value

What is the best way to achieve this?

I was thinking along the following lines: have a binary number which represents all the user preferences: e.g. 1100011100.. each bit corresponds to a particular preference

and then either save it as type BINARY (where I can store 255 bits i.e. 255 preference settings - is that right?) or covert the binary into int and store it as an int (then the choice is between INT or BIGINT - INT = 4*8 = 32 bits, BIGINT = 8*8 = 64 bits)

That way I only need to have 1 extra column in my user's table which stores the preferences and its easy to compare preferences between two users by simply taking the binary number

Does anyone have any other ideas about how to do what I am trying to do or see a problem in the way I am trying to do things here?

(Note Databases are not my strong point)

score 2 · Answer 1 · answered Apr 14 '12 at 20:55

I would suggest staying away from the BINARY Bitmask option as it makes querying for users with specific settings very difficult.

One way I have seen this done is with a key value pair in the database.

SettingsTable
(
  FkUserId int,
  SettingKey varchar(1024),
  SettingValue varchar(1024)
);

This doesn't scale well with a large number of users, but it makes it easy to add any number of key/value combinations without modifying the datbase.

You could do something similar in a more efficient matter with a settingsKey table and a settings values table.

SettingsKeys
(
  KeyId int,
  SettingKey varchar(1024)
);



SettingsTable
(
  FkUserId int,
  FkKeyId int,
  SettingValue varchar(1024)
);

Which would be more efficent than the orginal settings table, and you would be able to store any number of possible settings without having to modify the schema.

Just a couple of ideas.

Could you elaborate? What is large number of users? Is 5000 users acceptable? — hfatahi, Jan 06 '16 at 18:47

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

your Integer Idea sounds the best approach to the problem, you can have a stored proc or equivalent in mysql that will convert your integer to a bit representation too. you can look at the following question SQL Server Convert integer to binary string for details on how to do the same.

edited May 23 '17 at 11:55

Community

1
1

answered Apr 12 '12 at 06:54

Baz1nga

15,485
3
35
61

score 0 · Answer 3 · answered Apr 12 '12 at 16:06

Will your database ever have to answer the question:

For given preference, which users have it?

Encoding bits within a field prevents the DBMS from indexing each of these bits and answering the question above efficiently.

In general, violating the principle of atomicity (and therefore 1NF) should be justified by some very good reasons indeed. Truth to be told, you might actually have such reasons here, in space savings and performance for a specific (narrow) class of queries. Just make sure the flexibility you'll loose will never actually be needed in the future.

Best way to store user preferences for a website in MySQL - BINARY or INT

3 Answers3