I am building a database of users for our website. It is an MSSQL database. Currently I have all the usual fields eg First Name, Last Name, email etc. There are different things that our users need to have access to on our site. At the current time the number of 'functions' they need to be allowed or not allowed to use is 6. My problem comes from the fact that this number will need be extended as more functionality comes to the site.
In a previous attempt I had a 'bit' column directly in the user table for each of the functions. As you can imagine this is in no way extendable because if something new gets added I would need to change all the code and the database itself for a new functionality.
My current thinking is that I will have a seperate table for user functions. This will basically just be the ID of the user and a number representative of the function. In searching if a user allowed to do something it would be something like
SELECT Count(*) FROM user_functionality WHERE userId = @userid AND function = @function
If that row exists(count = 1) they are allowed, if not(count = 0) they are not allowed.
I can see this will work, but is it a good way to go about doing this? My user table will start with 500 users from an existing spreadsheet but is expected to grow by about 100 per year (not much I know).
Thanks for your help.