Recursive Relationships - Discussion

Sooo, I finally have the chance to tear apart some of the horrible structures that live in one of my databases.

Currently it Tracks systems access levels across the company, and to do this we create Roles that are loosely based on the company structure and comprise of 4 constituent parts in the name and a bunch of stuff that govern the access level of accounts in that role on the system in question.

To handle this I have 4, interrelated, Tables called role 1, role 2 and so on which contain simply the descriptor of the role part that they contain, so that [Role 1] might contain "Finance", [role 2] might contain "payroll", [role 3] "contrator payments", [role 4] "payments administrator".

Role 1 is related to role2,3,4 and so on up the chain and each individual role table is related to the "master" Role definition which contains the access level information for the system in question.

I'm hoping at this point that everyone currently looks similar to :eek:

If not, let me add that A role can currently contain either [role 1],[role 2][role 3] and a placeholder "#no level 4#" or can contain a "proper" descriptor in [Role 4].

Because of the design, we currently have 3000+ "no level 4#"s held in [Role 4] (wheres the slap head smiley when you need it?)

Now I've been looking at a number of ways of trying to Normalise and improve this part of the DB, the obvious solution, because role 1-4 tables are purely descriptors is to just combine all of those into one "role" table, stick a junction table between it and the Role Definition table and be done with it. However this still leaves several problems, we're still, sort of, hardcoded to 4 levels within the database itself (ok so we can just add another column if we need more) and a few other obvious failings.

Still with me?

So I've started to look into the possiblity of using a recursive relationship on what is still, in effect, the Junction table between the descriptors and the Role Definition.

At a basic level I now have 3 tables:

Role
----
RoleID - PK
Description - varchar

Roleconfig
----------
ConfigID - PK
ParentconfigID - int
RoleID - int

Roledefinition
-------------
ID
RoleconfigID


ParentconfigID relates to ConfigID within RoleConfig

At the moment, this structure, again at a basic level, now appears to work. However the variable elements within a role looked like a potential problem. Finding element one is simple, the [partentconfigID] is NULL. Finding the Top element when you've got 4 is simple, [configID] doesn't appear in [parentconfigID].

Where the fun starts is trying to control the recursion where you've got role1,role2, role3 being a valid role description and a role4 added to it also being a valid role description. Now as far as I can see there are two options to handle this.

1) Create in Roleconfig an entry (ok, entries) for role1,2,3 and use that as your 3 element role description. Create new entries containing the same information for your 1,2,3,4 role element. Less than ideal for, I hope, obvious reasons, we're still basically duplicating information and it is also difficult to build your role description in a query because you don't know how many elements will comprise that description.

2) Add a "valid" boolean column to roleconfig so that you can reuse your existing 1,2,3 and simply tag role 3 as 'valid', then add a role4 element and also tag that as 'valid'. The main downside to this is similar to the last one above, you know that valid means it is a top level description, but you still don't know how many elements there are and outputting a list containing

Finance-Payroll-ContractorPayments-PaymentAdmin
AND
Finance-Payroll-ContractorPayments

As valid roles still looks like it requires some jiggery-Pokery

I still have some concerns about controlling the recursion and ensuring that roledefinition can only relate back to a valid top level role which looks like it will require some careful planning. It's necessary to create a validation rule so that parentconfigID cannot be the configID for example, and I'll need to ensure that Roledefinition cannot relate to a roleconfig that isn't the last element in the chain.

We already "shoehorn" what are effectively 5+ element role descriptions into this structure, using recursion like this, I believe, eliminates the need for future Database changes if the front end code is amended to handle it. Which I guess is where the "discussion" part of the thread title comes in.

Sorry for the length of the thread, but this is melting my brain at the moment and it's not something that seems to come up very often so thought it might be interesting.