1

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?

  • 4
    Why do you believe creating N tables with only one foreign key is better than storing scalar information about an entity in that entity? – Kilian Foth May 7 '14 at 8:41
  • 1
    Overdesign detected. – Deer Hunter May 7 '14 at 8:45
  • It's really about me being annoyed by the over-whelming number of zeros. It does save on space, even if its negligible. But if you believe I'm not really gaining much, I'll go back to scalar attributes. – user127741 May 7 '14 at 8:48
  • If your database supports sets, that could be ideal for this situation. E.g. see dev.mysql.com/doc/refman/5.0/en/set.html – Rory Hunter May 7 '14 at 9:49
  • 1
    If you use a bit field in a database in its own table rather than use an integer field, while it's true you're saving 3 (usually) bytes, you're requiring an identifier column which usually takes up 8 bytes, so at best you're losing 5 bytes per record. It's not worth it, and even if it were, you have to ask yourself if you're not seriously compromising the simplicity of your project in doing so. In any case, a TINYINT type is usually one byte long and can hold 8 individual bits of data if that is so important to you. – Neil May 7 '14 at 9:51
1

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

  • Deserves a beer! So, create AND read is (attribute & 3) = 3 – Magno C May 7 '14 at 11:01
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').

enter image description here

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:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy