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?