2

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.

MDCCL
  • 8,530
  • 3
  • 32
  • 63
okainov
  • 141
  • 3

2 Answers2

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.

Glorfindel
  • 2,205
  • 5
  • 19
  • 26
1

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
Joe W
  • 1,058
  • 9
  • 20