My project needs to have a number of administrators, out of which only one will have super-admin privileges.
What is the best way to represent this in the database?
A:
Simple, yet effective: UserId = 1. Your application will always know it is the SuperUser.
Marcos Buarque
2010-08-18 04:49:16
So you're proposing to change user IDs of people on the fly? That's likely to bugger up all sorts of relationships elsewhere :-)
paxdiablo
2010-08-18 04:55:52
??? Not proposing to change user IDs on the fly. Application will automatically create a superuser when it is started the first time. If I am not mistaken, this is more or less the approach taken by Drupal. UserId, the first user automatically created into the database is userid 1
Marcos Buarque
2010-08-18 05:11:45
But if the current superadmin leaves the organization, they would have to change UserIDs for both the old (leaving) superadmin and the new (entering) superadmin. While technically achievable, it will be a lot of work to ensure ALL references to UserIDs in other tables are also updated. Similarly, most people working on the code are likely to assume that a single user always has the same UserID, and that a single UserID always refers to the same user.
Peter Leppert
2010-08-18 22:14:06
That is why I said it is a "simple" approach.
Marcos Buarque
2010-08-20 14:54:36
+2
A:
There are a few ways to do this.
Number 1: Have a column on your administrator (or user) table called IsSuperAdmin and have an insert/update trigger to ensure that only one has it set at any given time.
Number 2: Have a TimestampWhenMadeSuperAdmin column in your table. Then, in your query to figure out who it is, use something like:
select user_id from users
where TimestampWhenMadeSuperAdmin is not null
order by TimestampWhenMadeSuperAdmin desc
fetch first 1 row only;
Number 3/4: Put the SuperAdmin user ID into a separate table, using either the trigger or last-person-made-has-the-power approach from numbers 1 or 2.
Personally, I like number 2 since it gives you what you need without unnecessary triggers, and there's an audit trail as to who had the power at any given time (though not a complete audit trail since it will only store the most recent time that someone was made a SuperAdmin).
The trouble with number 1 is what to do if you just clear the current SuperAdmin. Either you have to give the power to someone else, or nobody has it. n other words, you can get yourself into a situation where there is no SuperAdmin. And number 3 and 4 just complicate things with an extra table.I
paxdiablo
2010-08-18 04:50:27
+2
A:
Use a roles/groups approach. You have a table containing all the possible roles, and then you have an intersect table containing the key of the user and the key of the role they belong to (there can be multiple entries per user as each user could have several roles (or belong to several groups)).
Also, don't call them super admin - just admin is fine, call the rest power user or something similar.
slugster
2010-08-18 04:52:53
I would call them 'admin' and 'manager' (and maybe 'user' or 'registered' for regular users and 'anonymous' for the rest). But the role/group approach is in my opinion surely the most powerful one, so I recommend that.
tux21b
2010-08-18 05:05:16
Yes, it's a good approach but, unfortunately, doesn't address the actual question, which was how to ensure there was only one SuperAdmin at a time. As to what you call them, it's irrelevant. If you want a `DemiGodEmporerOfTheUniverse`, that's fine by me :-)
paxdiablo
2010-08-18 05:28:30