What's the better way to structure this data within a database?

Question

We're doing a little redesign and consolidation of some tables in a database. Where we once had 2 tables, 'administrators' and 'users' we're now combining into a single table called 'users'. To facilitate that change we've created a 'user_types' table and a 'user_user_types' table which is the one-to-many linking table between 'users' and 'user_types'.

The reason we have to use the 'user_types' table is because there are different types of administrators, one being a super admin who has access to everything in our system. In the old set up there was a bit field in the 'administrators' table called 'SiteAdmin' which indicated that that particular admin was a super admin or not. Since shifting to this new system my thought was there would be a user type in the 'user_types' table that would be a super admin and we'd just link the correct user to that user type in those cases however, my fellow programmer here says he still wants to use a 'SiteAdmin' bit field in the new 'users' table. I think that is a bit redundant but he claims there would be excess load and it would be more processor intensive on the SQL server to have to join the 'users', 'user_types' tables together to determine if a particular user was a super admin.

My question is which way is better? Is the hit on the SQL server so great in joining the two tables that it warrants adding the bit field to the 'users' table to flag super admins?

Thanks!

Answer 1

I would find it strange if there was much of a hit to performance joining to what will be a small table.

What could be a bigger problem is storing differnt types of users two differnt ways which is confusing for maintenance. I think your solution is more straightforward for the long term.

Answer 2

Performance difference will depend on how much data is in these tables. If your talking about a few hundred or thousand user rows, you won't see any difference between solutions. If you have millions of users, and maybe a great amount of simultaneous access to the data, you may see a difference.

Answer 3

Your re-design seems reasonable and more maintainable down the road. I doubt the join will impact performance, especially since you are probably querying for the user type(s) once per user upon login.

You could write some unit tests against both designs, fill the tables with a bunch of fake data and run a timer against the join versus the one with use of a flag.