CODEWITHSUNDEEP
databasedatabase-designdatabase-normalization
TheOne
TheOne

Reputation: 11159

What is a sensible approach for enabling/disabling features on a database object?

Say I have a User database table with the regular username, password, email fields. What is a sensible way to add additional boolean fields that enable/disable features for any given user.

e.g.,

user_can_view_page_x
user_can_send_emails
user_can_send_pms
etc

Adding a bunch of boolean columns to the existing user table seems like the wrong way to go.

Upvotes: 0

Views: 82

Answers (2)

Brent Worden
Brent Worden

Reputation: 10984

I would use three tables.

One is your existing user table:

USER table
----
user_id (pk)
name
email
...

Another is a table containing possible user privileges:

PRIVILEGE table
----
privilege_id (pk)
name

Lastly is a join table containing an entry for each privilege setting for each user:

USER_PRIVILEGE table
----
user_id (pk) (fk)
privilege_id (pk) (fk)
allowed

Here is some sample data for two users, one with the send email privilege and the send pms privilege and another with a view page privilege:

USER data

USER_ID  NAME   EMAIL
-------  -----  -------------------
1        USER1  [email protected]
2        USER2  [email protected]

PRIVILEGE data

PRIVILEGE_ID  NAME
------------  -----------
1             view_page_x
2             send_email
3             send_pms

USER_PRIVILEGE data

USER_ID  PRIVILEGE_ID  ALLOWED
-------  ------------  -------
1        1             'N'
1        2             'Y'
1        3             'Y'
2        1             'Y'
2        2             'N'
2        3             'N'

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166486

Yes, I would think that this is the wrong approach.

I would rather create a 

User_Features Table

with columns something like

UserID
FeatureName

And check if a given user has the feature in question enabled/entered in the table.

You could even go as far as creating a Users_Groups table, where users are also assosiated with groups and features can be inherited/disallowed from group settings.

Upvotes: 1

Related Questions