I have a table which stores user information, one column stores their ID. I had implemented four different attributes in the same table which stored a bit indicating which file operations that user could perform (read, create, update, delete).
Thankfully, I realized that was terrible implementation and instead created four different tables with one column (the userID) which indicated which actions they could perform. It’s really about me being annoyed by the over-whelming number of zeros. It does save on space, even if it’s negligible.
However, this database is also queried by an application in an OOP language, and one of the objects frequently created is a User. This class naturally stores which file operations the User can perform. In the old implementation, these (boolean-valued) object fields could be quickly populated from the user table, but not now.
Now I’m confused on how I should populate these object fields. I feel like I should be able to perform a single query telling me which permission tables the user belongs to, but cannot find the write syntax (if it even exists).
So my question is: is there a way to perform a single query to figure out which tables (which only consist of a single column) a value occurs in? Or do I have to query each table independently to fill these object fields?
5
It’s not a good idea to separate those attributes to their own tables. It makes your data unorganized.
If your attributes are simple boolean type, you can combine them as one numeric value.
Just think of each attribute as a specific bit position.
For example
read : 1st bit
create : 2nd bit
update : 3rd bit
delete : 4th bit
Use the binary or operator to create your attribute value.
read + update = 1 | 4 = 5
read + delete = 1 | 8 = 9
To query, you may use this syntax
– find all user with update attribute:
select * from table1 where (attribute & 4) = 4
– find all user with delete attribute:
select * from table1 where (attribute & 8) = 8
1
My two cents:
This model allows for other types of actions besides “file actions” (operations, sorry I’d already created the model with ‘action’ instead of ‘operation’).
The query to see what file actions user 3 has permission to perform is (you can easily create a view based on this query leaving out the where clause) :
select
a.action_code
from
user u join permission p on (u.user_id = p.user_id)
join action a on (p.action_code=a.action_code)
join action_type t on (t.act_type_code=a.act_type_code)
where
u.user_id = 3 and
t.act_type_code = 'FILE';
Which will yield this:
