Structure for managing user access on a site

Question

we have a website where the left navigation has lot of links. These links are painted based on a users 'function'. There is a screen where an Admin user can Create a new function and assign it to some users.
The problem is that once the new function is created, our code has to be changed because inside our left navigation page we have lot of if/else clauses, based on which we paint the links.

Following is the structure of the concerned tables:

cmp_user_role [userid, role_id]
cmp_function [function_id, function_name, url]
cmp_function_role [role_id, function_id]

Sample Data:

cmp_user_role [userid, role_id]
 M23423    18
 H23414    21
 G23431    44

cmp_function [function_id, function_name, url]
0    SystemAdmin    Null
1    Debt Access    Null
8    Audit Reports  Null

cmp_function_role [role_id, function_id]
18    0
18    1
21    8
44    1

The query that brings list of functions relative to a userid is:

SELECT f.function_id, f.function_name
FROM cmp_function f, cmp_function_role p 
WHERE p.role_id 
IN (SELECT DISTINCT role_id FROM cmp_user_role 
WHERE user_id = 'M23423' ) 
AND p.function_id=f.function_id

and in our code we are doing (pseudo)

if (retrievedFunctionFromDB == "SystemAdmin")
  show links pertaining to system Admin
if (retrievedFunctionFromDB == "Debt Access")
  show links pertaining to Debt Access
...

I think everything is ok other than when user decides to create a new function and assigns that function to some user. In that case our code does not have that function name since we are hardcoding it. ..

what is the best strategy to make this dynamic w/out changing much of the table structure.

Answer 1

I think you could add table

cmp_function_links [function_id, link_url, link_text]

And the pseudocode (for printing links) should be

fetch all roles where user is
for each role
    print all links

I suppose your links are static, not dynamic (like link to the newest user, etc).