Two examples:
Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc).
Table articles, each article may have or not have some of ~10 possible attributes (like "for children", "18+", "less than 10 minutes to read", "long read").
What is the canonical \ more efficient \ fastest way to store it in the database?
I can think of two ways:
Option 1:
Bit-like kind of column, have column "permissions"\"attributes" and store a value which can be bit-interpreted like "0101100010", i-th bit is the flag for i-th attribute\permission
Option 2:
Relationship. Create table user_permissions, put 10 values with their IDs there, then create table user_permissions_map and keep this many to many (M:N) relationship in this table.
I'm a bit afraid of option 2 because it looks like it will require additional queries\lookups in permission_map table each time I need to check user's permission. When with option 1 it's just a column belonging to the user, makes it much simpler to check the permissions.
Best Answer
The bit-like field will make it harder to find all users or articles with a certain property. For users/permissions, that's not much of a problem (unless you want a list of e.g. all administrators). For articles, I can imagine that you want to filter on certain attributes (e.g. only non-18+ articles). You also have the option to store those fields (option 3) as single bit columns; then, no extra table is needed.
Another consideration: what happens when you want to add an additional permission/attribute which is by default set to '1'/'true'? For the bitmask field (option 1), this can be done by adding the right power of 2 to all values in column
permissions; for option 2, you need a massINSERTinuser_permissions_map; adding an extra bit column withADD COLUMNandDEFAULT true(option 3) is clear and easy.