Sample Header Ad - 728x90

Bit column vs relationships?

2 votes
2 answers
155 views
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.
Asked by okainov (141 rep)
Sep 18, 2018, 04:18 PM
Last activity: Sep 18, 2018, 05:16 PM