Referential Integrity for restricted areas?

[idkwhy]

Hello!

 

I'm currently designing my user management system and I want to add levels that restrict users from certain places on the site. Right now, my database is designed so that on the users table there is a BOOL column specifically for people who have restricted access and for people who don't. 0 means unrestricted access and 1 means something is restricted. If a user is restricted, a table with all the restriction information is joined. Right now, there is an "area" column. That's going to be an integer which references the area (represented by a number). I'm just wondering, is it better to hard code it or reference it from a table in the database according to Referential Integrity?

 

Thanks in advance to all replies!

Edited August 30, 2013 by idkwhy

[vinny42]

 

I'm just wondering, is it better to hard code it or reference it from a table in the database according to Referential Integrity?

 

A number works for your application, but it means nothing to you as a human being, so a name is required. You can use a lookup table that links the number to an integer, that would restrict the possible values to only those you have defined. Performancewise; you're never going to use that lookup table anywhere other than in the bit where you display the rights that can be assigned, probably in some admin interface.

 

You could store the names in the application, but then your application must be in sync with the database at all times, which sounds easy enough but it will bite you when you do silly things like upgrade your website where either the database or the application will be upgraded first.

 

However, I'd make a few changes in your setup; right now you have an optimistic setup; nobody is restricted unless there are restrictions defined for them. Normally you'd want the opposite; everybody is completely restricted unless there are records that give them access to particular areas.

 

If you use a role based system you eliminate the need for the "is restricted" boolean and save yourself a lot of admin work.

[idkwhy]

Thank you so much! You have pushed my thinking in the right direction