Last post Sep 27, 2014 07:22 PM by Lannie
Sep 22, 2014 02:07 PM|matt.gulick|LINK
I am designing a centralized management GUI to manage all manner of things in my companies intranet. What I am stuck on is part of the security I am trying to design. I can't get the normalization of the tables correct.
essentially my concept is as follows:
So I start out with 4 tables:
Now clearly I need a few more tables that link the IDs together. Initially I had FK_RoleID in the USERS table, but when the scope expanded to include multiple webs, I had to account for a user having different roles in different webs.
So a USER can have one ROLE per WEB, but can be in multiple ROLES across multiple WEBS.
Additional "linking" tables
Then I began thinking how that would work in a GUI
User is selected from a list ............. for editing ............. to add USER to the 'someweb.com' WEB with 'admin' ROLE
There would have to be a table that contains a list of ROLES for each WEB so that would be i also need:
Also since there can be the same ROLE used in multiple WEBS that the TRANSACTIONS assigned also could be different
You can see how complex this is getting and I am kind of starting to lose my place already.
So to reiterate
Is there another way to think about this?
Thanks for you time and insight.
Sep 27, 2014 01:30 PM|Lannie|LINK
You are getting into MANY to MANY relationships which requires a JOINING table.
One patient can have many doctors
One doctor can have many patients
Then throw in VISITS to a health care facility
Ah but the patient can visit many facilities
and the doctor can work in many facilities
Now you can see how you are getting blurry vision real fast right?
So then you use LINKING table to resolve many to many relationships back into one to many relationships to maintain referential integrity
PATIENT LINKPATIENTDOCTOR DOCTOR
PK PATIENTID PK LINKID PK DOCTORID
So now if I want to know which doctors a patient has seen I go to the many to many resolver, the linking table
Now apply that to USERS, ROLES, APPLICATIONS
Users can have many Roles
Roles can have many Users
You need a linking table for Users and Roles
in fact you will need many linking tables since Users, Roles, Applications is kind of a TRIANGLE of many to many relationships.
Sep 27, 2014 04:36 PM|matt.gulick|LINK
Thank-you for you reply, I wondered if anyone ever would.
I had a moment of clarity a couple days ago and came up with the following as the "linking" table structure.
Its still early in testing to say this is it, but so far it has been working.
Here is the accompanying SQL that resulted from the schema.
SELECT COUNT(*) FROM RolesTrans
RIGHT OUTER JOIN Trans
LEFT OUTER JOIN WebsTrans ON Trans.TransId = WebsTrans.FK_TransId ON RolesTrans.FK_WebTranId = WebsTrans.WebTransId
LEFT OUTER JOIN UsersRoles ON RolesTrans.RoleTransId = UsersRoles.FK_RoleTransId
WHERE (WebsTrans.FK_WebId = @webId )
AND (UsersRoles.FK_UserId = @userId )
AND (Trans.StaticId IN ( @values ))
Sep 27, 2014 07:22 PM|Lannie|LINK
Exactly moving in the right direction. But it sure can get complex at times!!