September 17, 2015 at 11:26 am
At work I am trying to design a common database to handle user access to applications and designated functions therein. We will be using AD authentication and using tables to control application access -- Multiple users & multiple applications. Requirement is a single set a tables to control access for all users, all applications and roles, groups & functions within an application.
Each user might be able access multiple applications
Each application has multiple users
Each application has many functions and one or more functions will be grouped together for access control -- Each group can have one or more functions but the groups are specific to one particular application.
Each application user can be assigned one or more roles within an application
Each application role can get access to one or more application function groups
...Hopefully I have explained it well enough
My script looks like I have everything covered from a relational standpoint but a sanity check would be most appreciated if possible (too many many-to-many :w00t:)
create table dbo.Users
(UserNamevarchar(35)not null
, UserKeyintidentity(1,1)not null
, constraint pk_User primary key clustered
(UserKey asc)
)
go
create table dbo.Applications
(ApplicationNamevarchar(50)not null
, ApplicationKeyintidentity(1,1)not null
, constraint pk_Applications primary key clustered
(ApplicationKey asc)
)
go
create table dbo.ApplicationUsers
(UserKeyintnot null
, ApplicationKeyintnot null
, constraint pk_ApplicationUsers primary key clustered
(UserKey, ApplicationKey)
, constraint fk_ApplicationUsers_UserKey foreign key (UserKey)
references Users(UserKey)
, constraint fk_ApplicationUsers_ApplicationKey foreign key (ApplicationKey)
references Applications(ApplicationKey)
)
go
create table dbo.ApplicationFunctions
(ApplicationKeyintnot null
, FunctionCodevarchar(10)not null
, FunctionDescriptionvarchar(300)null
, ApplicationFunctionKeyint identity(1,1)not null
, constraint pk_ApplicationFunctions primary key clustered
(ApplicationKey, ApplicationFunctionKey)
, constraint uq_ApplicationFunctions unique (ApplicationKey, FunctionCode)
, constraint fk_ApplicationFunctions_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
)
go
create table dbo.ApplicationRoles
(ApplicationKeyintnot null
, RoleNamevarchar(20)not null
, RoleDescriptionvarchar(150)null
, ApplicationRoleKeyint identity(1,1)not null
, constraint pk_ApplicationRoles primary key clustered
(ApplicationKey, ApplicationRoleKey)
, constraint uq_ApplicationRoles unique (ApplicationKey, RoleName)
, constraint fk_ApplicationRoles_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
)
go
create table dbo.ApplicationUserRole
(auUserKeyintnot null
, auApplicationKeyintnot null
, arApplicationKeyintnot null
, arApplicationRoleKeyintnot null
, constraint pk_ApplicationUserRole primary key clustered
(auUserKey, auApplicationKey, arApplicationKey, arApplicationRoleKey)
, constraint eq_ApplicationUserRole_auApplicatioKey_arApplicationKey check (auApplicationKey = arApplicationKey)
, constraint fk_ApplicationUserRole_auApplicationKey foreign key (auUserKey, auApplicationKey)
references ApplicationUsers(UserKey, ApplicationKey)
, constraint fk_ApplicationUserRole_arApplicationKey foreign key (arApplicationKey, arApplicationRoleKey)
references ApplicationRoles(ApplicationKey, ApplicationRoleKey)
);
go
create table ApplicationGroups
(ApplicationKeyintnot null
, GroupNamevarchar(20)not null
, GroupDescriptionvarchar(150)null
, ApplicationGroupKeyint identity(1,1)not null
, constraint pk_ApplicationGroups primary key clustered
(ApplicationKey, ApplicationGroupKey)
, constraint uq_ApplicationGroups unique (ApplicationKey, GroupName)
, constraint fk_ApplicationGroups_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
)
go
create table ApplicationRoleGroups
(arApplicationKeyintnot null
, arApplicationRoleKeyintnot null
, agApplicationKeyintnot null
, agApplicationGroupKeyintnot null
, constraint pk_ApplicationRoleGroups primary key clustered
(arApplicationKey, arApplicationRoleKey, agApplicationKey, agApplicationGroupKey)
, constraint eq_ApplicationRoleGroups_agApplicatioKey_arApplicationKey check (agApplicationKey = arApplicationKey)
, constraint fk_ApplicationRoleGroups_agApplicationKey foreign key (agApplicationKey, agApplicationGroupKey)
references ApplicationGroups(ApplicationKey, ApplicationGroupKey)
, constraint fk_ApplicationRoleGroups_arApplicationKey foreign key (arApplicationKey, arApplicationRoleKey)
references ApplicationRoles(ApplicationKey, ApplicationRoleKey)
)
go
create table ApplicationGroupFunctions
(agApplicationKeyintnot null
, agApplicationGroupKeyintnot null
, afApplicationKeyintnot null
, afApplicationFunctionKeyintnot null
, constraint pk_ApplicationGroupFunctions primary key clustered
(agApplicationKey, agApplicationGroupKey, afApplicationKey, afApplicationFunctionKey)
, constraint eq_ApplicationGroupFunctions_agApplicatioKey_afApplicationKey check (agApplicationKey = afApplicationKey)
, constraint fk_ApplicationGroupFunctions_agApplicationKey foreign key (agApplicationKey, agApplicationGroupKey)
references ApplicationGroups(ApplicationKey, ApplicationGroupKey)
, constraint fk_ApplicationGroupFunctions_afApplicationKey foreign key (afApplicationKey, afApplicationFunctionKey)
references ApplicationFunctions(ApplicationKey, ApplicationFunctionKey)
)
go
Thank you
Edit to add attachment
September 28, 2015 at 12:52 pm
Calling something by different names in different places, as you have done here, leads to crazy things like holding the same thing twice with two column names in one table, necessiitating check constraints to guarantee that the sameness isn't violated. Maybe you've done this because you mistakenly think that a given column can only be part of one foreign key on thetable containing it?
Apart from that nothing strikes me as off colour but I haven't really checked it properly.
Tom
September 28, 2015 at 1:16 pm
I think it will work. Multi-column primary keys fry my brain on gnarly queries. I think that is why you did not follow names through to lower tables.
I don't understand why you create a multi-part primary key on ApplicationFunctions since ApplicationFunctionKey is unique and says what a row really means.
create table dbo.ApplicationFunctions
(ApplicationKeyintnot null
, FunctionCodevarchar(10)not null
, FunctionDescriptionvarchar(300)null
, ApplicationFunctionKeyint identity(1,1)not null
, constraint pk_ApplicationFunctions primary key clustered
(ApplicationKey, ApplicationFunctionKey)
, constraint uq_ApplicationFunctions unique (ApplicationKey, FunctionCode)
, constraint fk_ApplicationFunctions_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
)
go
September 28, 2015 at 3:09 pm
TomThomson (9/28/2015)
Calling something by different names in different places, as you have done here, leads to crazy things like holding the same thing twice with two column names in one table, necessiitating check constraints to guarantee that the sameness isn't violated. Maybe you've done this because you mistakenly think that a given column can only be part of one foreign key on thetable containing it?Apart from that nothing strikes me as off colour but I haven't really checked it properly.
Tom -- Thank you for taking a look. I am not sure I follow what you are referring to by the name changes? Are you referring to the ApplicationKey traversing through the relations where I use an abbreviation of the preceding table to prefix the key traversal (I just looked again and that is all that I am catching)?
Thank you.
September 28, 2015 at 3:19 pm
Bill Talada (9/28/2015)
I think it will work. Multi-column primary keys fry my brain on gnarly queries. I think that is why you did not follow names through to lower tables.I don't understand why you create a multi-part primary key on ApplicationFunctions since ApplicationFunctionKey is unique and says what a row really means.
create table dbo.ApplicationFunctions
(ApplicationKeyintnot null
, FunctionCodevarchar(10)not null
, FunctionDescriptionvarchar(300)null
, ApplicationFunctionKeyint identity(1,1)not null
, constraint pk_ApplicationFunctions primary key clustered
(ApplicationKey, ApplicationFunctionKey)
, constraint uq_ApplicationFunctions unique (ApplicationKey, FunctionCode)
, constraint fk_ApplicationFunctions_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
)
go
Bill - Thank you for taking the time to look...I need to see what I was thinking for doing that. I look at it again now and am wondering why...
Thank you
September 29, 2015 at 4:36 am
Not sure if this is worth mentioning ... but ... 🙂
identity(1,1)
We start IDENTITY at a higher number, usually the order of magnitude that we expect the row count to become / initially, so that numbers are the same width. We think it helps with debugging etc. but it may just be me being pedantic!
We start each table at a different starting point (for us usually 1,000 apart) so then when we have only a handful of rows during DEV there is no chance that we accidentally join two tables on the wrong ID column name - i.e. if we have UserIDs 1000-1010, and ApplicationIDs 2000-2010 then if we accidentally JOIN ON UserID = ApplicationID we will get zero rows and the problem will come to light sooner.
September 29, 2015 at 5:34 am
Bill Talada (9/28/2015)
I think it will work. Multi-column primary keys fry my brain on gnarly queries. I think that is why you did not follow names through to lower tables.I don't understand why you create a multi-part primary key on ApplicationFunctions since ApplicationFunctionKey is unique and says what a row really means.
...
The compound key was needed for the foreign key constraint in the next table for the many-to-many
ApplicationFunctions(ApplicationKey, ApplicationFunctionKey) => ApplicationGroupFunctions(afApplicationKey, afApplicationFunctionKey,...)
Functions for one application cannot be combined in a group of functions for a different application -- All application functions in the group must be for the same application.
Thank you
September 29, 2015 at 5:47 am
TomThomson (9/28/2015)
Calling something by different names in different places, as you have done here, leads to crazy things like holding the same thing twice with two column names in one table, necessiitating check constraints to guarantee that the sameness isn't violated. Maybe you've done this because you mistakenly think that a given column can only be part of one foreign key on thetable containing it?Apart from that nothing strikes me as off colour but I haven't really checked it properly.
I think I understand what you are referring to -- the junction table. I need to be sure that there is no "cross pollination" between applications with respect to groups, functions, roles...
Roles, functions and groups of functions -- Each is for a specific application.
Users of application "A" cannot be associated with roles for application "B" (only for "A").
Roles for application "A" cannot be associated with Groups for application "B" (only for "A").
Groups for application "A" cannot contain functions for application "B" (only for "A").
...
This is where I was/am running into trouble -- guaranteeing everything stays within a single application
Thank you
September 29, 2015 at 6:03 am
Bill Talada (9/28/2015)
I think it will work. Multi-column primary keys fry my brain on gnarly queries. I think that is why you did not follow names through to lower tables.I don't understand why you create a multi-part primary key on ApplicationFunctions since ApplicationFunctionKey is unique and says what a row really means.
...
OK, so I thought through where I was having the problem with the junction table relations and made the following changes:
create table dbo.ApplicationFunctions
(ApplicationKeyintnot null
, FunctionCodevarchar(10)not null
, FunctionDescriptionvarchar(300)null
, ApplicationFunctionKeyint identity(1,1)not null
, constraint pk_ApplicationFunctions primary key clustered
(ApplicationFunctionKey)
, constraint uq_ApplicationFunctions unique (ApplicationKey, FunctionCode)
, constraint fk_ApplicationFunctions_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
, constraint uq_ApplicationFunctions_FK unique (ApplicationKey, ApplicationFunctionKey)
)
go
create table dbo.ApplicationRoles
(ApplicationKeyintnot null
, RoleNamevarchar(20)not null
, RoleDescriptionvarchar(150)null
, ApplicationRoleKeyint identity(1,1)not null
, constraint pk_ApplicationRoles primary key clustered
(ApplicationRoleKey)
, constraint uq_ApplicationRoles unique (ApplicationKey, RoleName)
, constraint fk_ApplicationRoles_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
, constraint uq_ApplicationRoles_FK unique (ApplicationKey, ApplicationRoleKey)
)
go
create table ApplicationGroups
(ApplicationKeyintnot null
, GroupNamevarchar(20)not null
, GroupDescriptionvarchar(150)null
, ApplicationGroupKeyint identity(1,1)not null
, constraint pk_ApplicationGroups primary key clustered
(ApplicationGroupKey)
, constraint uq_ApplicationGroups unique (ApplicationKey, GroupName)
, constraint fk_ApplicationGroups_ApplicationKey foreign key (ApplicationKey) references Applications(ApplicationKey)
, constraint uq_ApplicationGroups_FK unique (ApplicationKey, ApplicationGroupKey)
)
go
Which allows me to create the foreign key relations. However, I believe this is the other issue I was having problems with and Tom was alluding to -- Carrying the ApplicationKey forward to guarantee everything stays within a single application.
Thank you
September 29, 2015 at 6:14 am
We start each table at a different starting point (for us usually 1,000 apart) so then when we have only a handful of rows during DEV there is no chance that we accidentally join two tables on the wrong ID column name - i.e. if we have UserIDs 1000-1010, and ApplicationIDs 2000-2010 then if we accidentally JOIN ON UserID = ApplicationID we will get zero rows and the problem will come to light sooner.
Interesting idea and good point 🙂
Thank you
September 29, 2015 at 12:09 pm
I see you prefix your constraints with pk, uk, ak, etcetera. May I suggest you begin almost all objects with TableName and just use suffixes. That way you can see all objects pertaining to a table very easily. It helps developers to know which objects are dependent on a table. Even stored procedures that pertain mainly to one table can be named that way.
I have strict naming conventions so my developers almost never need to look at the definition of a properly named object.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply