Bit column vs relationships?
Bit column vs relationships?
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).
users
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").
articles
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 attributepermission
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.
user_permissions
user_permissions_map
I'm a bit afraid of option 2 because it looks like it will require additional querieslookups 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.
permission_map
2 Answers
2
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 mass INSERT in user_permissions_map; adding an extra bit column with ADD COLUMN and DEFAULT true (option 3) is clear and easy.
permissions
INSERT
user_permissions_map
ADD COLUMN
DEFAULT true
Option 3 is adding extra columns, e.g.
ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.– Glorfindel
Sep 18 '18 at 16:40
ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true
I think, for option 2 "negative" attributes it can be done via some additional column in
user_permissions table like is_true_by_default (like property for attribute). However it will still require some backend logic changed. Adding columns to articles looks like non-intuitive solution as for me. First, it will spam the database with 10+ extra columns for each attribute (if use column for each attribute). Second, it can make backend logic complicated (if use column only for "negative" attribute and keep "positive" attributes in separated table)– The Godfather
Sep 18 '18 at 16:49
user_permissions
is_true_by_default
articles
@TheGodfather Adding Y/N columns to a table shouldn't have that much of an impact on it as the data is small in footprint and will be easy to store.
– Joe W
Sep 18 '18 at 17:05
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
Sep 18 '18 at 17:08
I would suggest doing a different design for each of those examples as I don't think that one design fits all cases even within a database design. In the case of user permissions that is something that is unlikely to change often but you may want to add article attributes more frequently as the site expands.
For user permissions I would suggest a simple user table with a column for each permission. Once it is set up it is unlikely that new permissions will be added and a simple table will allow for easy access to the data.
user_id NUMBER,
permission_1 VARCHAR2(1 CHAR) default 'N',
permission_2 VARCHAR2(1 CHAR) default 'N'
For article on the other hand I would suggest a mapping table so that it is easier to add new attributes as needed. In fact it would be possible for someone with the correct permissions to add a new attribute as they submitted an article that needed one.
attributes
attribute_id NUMBER,
attribute_description VARCHAR2(4000 CHAR)
article_attribute
article_id NUMBER,
attribute_id NUMBER
Thanks for contributing an answer to Database Administrators Stack Exchange!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy
I haven't really got how do you suggest to do this without extra table and what is your "option 3". Could you please provide more details?
– The Godfather
Sep 18 '18 at 16:36