Please advise on data model

[Stacey Howard]

Can some please review my small security data model and advise according
please. I have users which can be assigned to groups, and those groups can
have several rights associated it with them. Is my data model correct? In
the rights table is where I define my rights, these rights are actually
codes I use on forms in my application, just wanted to note that. Basically
I want to be able to create a user, assign that user to a group and be able
to assign that group certain rights. So when the user logs in, the rights
associated with his group with be used. Thanks. Stacey

Here are the tables with a few fields:
1. tblUser
-pkSecurityUser
-Name
-PWD

2. tblUersGroups
-pkUserGrpID
-fkSecurityUserID
-fkGroupID

3. tblGrpRights
-pkGrpRightsID
-fkGroupID
-fkRightsID
-permission (RO,RW)

4. tblGroups
-pkUserGrpID
-fkSecurityUserID
-fkGroupID

5. tblRights
-pkRightsID
-RightsDescription

Relationships:

tblUser 1-N tblUersGroups
tblUersGroups N-1 tblGroups
tblGroups 1-N tblGrpRights
tblGrpRights N-1 tblRights

 

[Ken Snell]

I'm a bit confused by your table structure because you seem to put unique
fields into junction tables (see "permission" field in tblGrpRights table,
when "permission" is related to the specific Right, not to the
GroupRights?).

Try this:

You have users:
tblUser table
UserID (pk)
UserName
UserPassword

You have groups:
tblGroups table
GroupID (pk)
GroupName

You have users assigned to groups:
tblGroupMembers table
GroupID (cpk with UserID)
UserID (cpk with GroupID)

You have rights:
tblRights table
RightsID (pk)
RightsName

You have rights assigned to groups:
tblGroupRights table
GroupID (cpk with RightsID)
RightsID (cpk with GroupID)


This should give you what you seek, I believe.

 

[Stacey Howard]

Ken,
Thanks for the advice. My rights will actually be the names of certain
form the user can access in the application. A static list if you will. I
will need to keep this list some where and thought that if I kept them as
RightNames in the rights table I could store the permission "RO", "NR" in
the tblGrpRights table in the permission field to determine the permissions
for a chosen right for a group. Any advice on what I'm trying to accomplish.

Your help is greatly appreciated.
-Stacey

 

[Ken Snell]

In this case, you might expand the fields in the tblRights table:

tblRights table
RightsID (pk)
FormName
RightsNameType

So with above, you could have some records that look like this:

RightsID FormName RightsNameType
1 frmOne ReadOnly
2 frmOne ReadWrite
3 frmTwo ReadOnly
4 frmTwo ReadWrite

Obviously, if you find that you're repeating the type, you might consider a
table of the available rights options, assign IDs to each of the available
options, and use that number in the RightsNameType field.

Using above, you could have as many different rights that you want for any
form; one form could have two rights, another form could have four rights,
etc.