Mixing
Bit column vs relationships?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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 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.
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.
database-design permissions many-to-many bit-manipulation
edited 2 hours ago
MDCCL
6,30731640
asked 2 hours ago
The Godfather
1113
add a comment |Â
up vote
1
down vote
favorite
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 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.
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.
database-design permissions many-to-many bit-manipulation
edited 2 hours ago
MDCCL
6,30731640
asked 2 hours ago
The Godfather
1113
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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 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.
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.
database-design permissions many-to-many bit-manipulation
edited 2 hours ago
MDCCL
6,30731640
asked 2 hours ago
The Godfather
1113
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 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.
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.
database-design permissions many-to-many bit-manipulation
database-design permissions many-to-many bit-manipulation
edited 2 hours ago
MDCCL
6,30731640
asked 2 hours ago
The Godfather
1113
edited 2 hours ago
MDCCL
6,30731640
asked 2 hours ago
The Godfather
1113
edited 2 hours ago
MDCCL
6,30731640
edited 2 hours ago
MDCCL
6,30731640
edited 2 hours ago
MDCCL
6,30731640
6,30731640
asked 2 hours ago
The Godfather
1113
asked 2 hours ago
The Godfather
1113
asked 2 hours ago
The Godfather
1113
1113
add a comment |Â
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
2
down vote
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.
answered 2 hours ago
Glorfindel
5101314
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
1 hour ago
Option 3 is adding extra columns, e.g.ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.
– Glorfindel
1 hour ago
I think, for option 2 "negative" attributes it can be done via some additional column inuser_permissionstable likeis_true_by_default(like property for attribute). However it will still require some backend logic changed. Adding columns toarticleslooks 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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
 |Â
show 1 more comment
up vote
1
down vote
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
answered 1 hour ago
Joe W
475210
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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.
answered 2 hours ago
Glorfindel
5101314
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
1 hour ago
Option 3 is adding extra columns, e.g.ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.
– Glorfindel
1 hour ago
I think, for option 2 "negative" attributes it can be done via some additional column inuser_permissionstable likeis_true_by_default(like property for attribute). However it will still require some backend logic changed. Adding columns toarticleslooks 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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
 |Â
show 1 more comment
up vote
2
down vote
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.
answered 2 hours ago
Glorfindel
5101314
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
1 hour ago
Option 3 is adding extra columns, e.g.ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.
– Glorfindel
1 hour ago
I think, for option 2 "negative" attributes it can be done via some additional column inuser_permissionstable likeis_true_by_default(like property for attribute). However it will still require some backend logic changed. Adding columns toarticleslooks 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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
 |Â
show 1 more comment
up vote
2
down vote
up vote
2
down vote
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.
answered 2 hours ago
Glorfindel
5101314
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.
answered 2 hours ago
Glorfindel
5101314
edited 1 hour ago
answered 2 hours ago
Glorfindel
5101314
answered 2 hours ago
Glorfindel
5101314
answered 2 hours ago
Glorfindel
5101314
5101314
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
1 hour ago
Option 3 is adding extra columns, e.g.ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.
– Glorfindel
1 hour ago
I think, for option 2 "negative" attributes it can be done via some additional column inuser_permissionstable likeis_true_by_default(like property for attribute). However it will still require some backend logic changed. Adding columns toarticleslooks 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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
 |Â
show 1 more comment
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
1 hour ago
Option 3 is adding extra columns, e.g.ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.
– Glorfindel
1 hour ago
I think, for option 2 "negative" attributes it can be done via some additional column inuser_permissionstable likeis_true_by_default(like property for attribute). However it will still require some backend logic changed. Adding columns toarticleslooks 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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
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
1 hour ago
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
1 hour ago
Option 3 is adding extra columns, e.g.
ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.– Glorfindel
1 hour ago
Option 3 is adding extra columns, e.g.
ALTER TABLE articles ADD COLUMN is_18_plus BIT NOT NULL DEFAULT VALUE true.– Glorfindel
1 hour ago
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
1 hour ago
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
1 hour ago
@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
1 hour ago
@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
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
One thing to consider is how often would you be adding in a new permission/attribute?
– Joe W
1 hour ago
 |Â
show 1 more comment
up vote
1
down vote
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
answered 1 hour ago
Joe W
475210
add a comment |Â
up vote
1
down vote
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
answered 1 hour ago
Joe W
475210
add a comment |Â
up vote
1
down vote
up vote
1
down vote
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
answered 1 hour ago
Joe W
475210
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
answered 1 hour ago
Joe W
475210
answered 1 hour ago
Joe W
475210
answered 1 hour ago
Joe W
475210
answered 1 hour ago
Joe W
475210
475210
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217951%2fbit-column-vs-relationships%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
