A Mysql Question.....

[eluser]glopv2[/eluser]
Perhaps thinking about how the data will be accessed will help. I am guessing that 99% of all DB queries will be something like

"Does user X have Y notification enabled? Yes or No"

I am guessing you'll never need to do a look-up like "Give me all the users who have Y notification enabled." That may guide you in setting up your table structure.

For instance, this might be an instance where you create a bit field variable, and simply store it as an unsigned integer*. The pro of this: saves lots of space**, doesn't have any impact on the "Does X have Y enabled" lookup speed. The con: loss of simplicity, harder for a human to read, you lose the ability to quickly ask "Who has Y enabled."

@mddd's version would make a lot of sense if you expect most people to keep the defaults. That way you only would store a much smaller "modifications" amount of data, rather than having a big ol' table of data that mostly repeats itself.



I hope this makes sense.


-----------------------------
* If the number is written in binary, e.g. 1101, then each "digit" of the binary number represents the yes/no of a certain field. so if you decide you have 4 notifications A, B, C, and D, and they map like A=>1000, B=>0100, C=>0010, D=>0001..... actually never mind just look on wikipedia. The point is you could query this integer/bit field out of the database and use bitwise operators to quickly determine the yes/no you're looking for.

** Saving the yes/no as an enum for each property will store each yes/no for each user as a byte. My way will use a bit. You should use 1/8 as much space.