Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 25th, 2004, 06:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
 I assumed that 'Permission' was a value which uniquely defined a, er, permission. Thus, no two rows would have the same permission value, so it was a primary key.
 As I said, I think I am using the wrong terminology. I mean a permission to be what amount of access a user or group is entitled to have for a certain section of software, therefore the management group may be allowed access to the administrative section but the support group would not be. This would mean that for the same permission there would be 2 possible values (allow access and do not allow access), one permission row would map to the management group and another to the support group.

Going back to your original idea (and without using a PermissionID column), how would you implement the concept I have described? I ask just to further my knowledge and understanding of relational databases and normalisation and as I have already said I will probably not implement your idea at the moment. I am using a dBASE compatible database engine, so it is great at flat files but rubbish at JOINing theses files together. When I finally convince the powers that be that we need to upgrade to a relational database engine I may well implement a more relational and normalised database.

Regards
Owain Williams
 
Old February 25th, 2004, 09:16 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

[quote
With all due respect to Joe, I don't agree with the notion of adding an identifier that distinguishes one row "type" from another. A row in a table is a model of a single entity (or process), and a table then is a set of these rows - thus a table should contain entries describing individual instances of the set of entities the table models. Having some rows model one kind of entity and others model a different sort violates one of the normal forms, I forget which (4th?).[/quote]
I was thinking of the native SQL tables, this is how they do it. For example all objects are stored in sysobjects with the type column differentiating whether they are a table or a stored proc etc.

--

Joe
 
Old February 26th, 2004, 11:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Let's start at the beginning, as I understand your requirements. Stop me if I misunderstand. :)

It seems to me that you have four distinct entities your tables must model, plus the relationships between them. The entities are Users, Groups, Software Sections, and Permission levels.

Both Users and Groups are granted/denied access to Software Sections, and that access is defined by the Permission Level.

For the sake of this discussion, let's define a few permission levels as: 'Full Access', 'Read only' and 'No Access'. This is essentially an enumeration of integer values, and is easily extended for your specific situation. I think it's helpful to arrange the permission values in some order of increasing/decreasing access level, so that code can for example make a menu entry visible if the permission level for the section the entry corresponds to is greater then or equal to 'x'.

So, we must have a table for Users, one for Groups, and one for Sections. It's interesting to contemplate whether a table should be created for the Permission level. This I think is a judgement call. You could simply "hard-wire" the values in your code, or you could make it a table and allow for more flexibility (and complexity) in their definition. I guess I would lean towards hard-wiring, since I wouldn't expect the values would change much if at all over the life of the application. Furthermore, the code that enables/disables menu entries, for example, needs to 'know' the valid permission levels, so your application will in effect hard-wire the values anyway.

The Users, Groups, and UserGroups tables we've already discussed. These tables define individual users and individual groups and the set of Groups each user is a member of.

We'll define a Software Sections table which lists the various sections that exist:

Sections
    SectionID
    SectionName
    <etc>

Now we must define the relationship that a user has to a software section. This relationship contains the User, the Section, and the permission level granted to this user in this section:

UserSections
    UserID
    SectionID
    Permission
    <etc>

The <etc> there could be for such things as an expiration date which defines the date when access is revoked. Thus, this table defines the relationship that a specific user has to a specific section and the permission level the user has for that section, plus any other data appropriate to describe or refine that relationship. No user data or Section data belongs in this table, only data relevant to the relationship itself.

A similar table would be created for GroupSections, defining the sections a group has access to and the associated permission level for that access.

I would query these tables by User and construct an in-memory structure that was a list of the sections the logged in user has access to and the associated permission level. This structure would be constructed by essentially created the UNION of the UserSections table and the GroupSections table for the given user. You'll need to decide how resolve conflicts when the user is explicitly given a certain level of access to a given section, but the user's membership in a group gives a different level. Take the minimum or maximum permission level of the two, whatever is appropriate for your situation.

As your application runs, it can query this in-memory structure for the software section in question, and assign access as indicated by the associated permission level.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old February 26th, 2004, 11:33 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Quote:
quote:
With all due respect to Joe, I don't agree with the notion of adding an identifier that distinguishes one row "type" from another. A row in a table is a model of a single entity (or process), and a table then is a set of these rows - thus a table should contain entries describing individual instances of the set of entities the table models. Having some rows model one kind of entity and others model a different sort violates one of the normal forms, I forget which (4th?).
I was thinking of the native SQL tables, this is how they do it. For example all objects are stored in sysobjects with the type column differentiating whether they are a table or a stored proc etc.
--

Joe
 
Old February 26th, 2004, 12:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

[quote]quote:Originally posted by joefawcett
Quote:
I was thinking of the native SQL tables, this is how they do it. For example all objects are stored in sysobjects with the type column differentiating whether they are a table or a stored proc etc.
Fair enough.

Those tables are describing system objects in general. The table represents the set of valid objects in the system, and for that usage, the type is an attribute of the object represented. In that context, all entries are objects and display more similarities from that perspective than differences.

I think in the subject case, though, Users and Groups are quite different entities and should be represented separately, IMO.

But there is more than one way to skin this cat, and there is rarely only one answer... ;)



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old March 1st, 2004, 07:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you to everyone that has contributed, it has certainly turned into an interesting thread. Special thanks to Jeff for your patients and detailed explanations.

Regards
Owain Williams
 
Old March 1st, 2004, 10:23 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Quote:
quote:
Those tables are describing system objects in general. The table represents the set of valid objects in the system, and for that usage, the type is an attribute of the object represented. In that context, all entries are objects and display more similarities from that perspective than differences.

I think in the subject case, though, Users and Groups are quite different entities and should be represented separately, IMO.
Interesting, another example I can give is this, we have a table of customers at work, they may be individuals or companies so the table is called tblLegalEntity. There is a type field to ditinguish and the table is recursive so that those of type "company" can contain others of type "company" or those of type "person", "person" types cannot contain other types. It is easy to model as one table but if we had two tables it would be more difficult. I look on Groups and Users similarly.

Regards


--

Joe
 
Old November 3rd, 2006, 07:44 PM
Registered User
 
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
 Let's start at the beginning, as I understand your requirements. Stop me if I misunderstand. :)

It seems to me that you have four distinct entities your tables must model, plus the relationships between them. The entities are Users, Groups, Software Sections, and Permission levels.

Both Users and Groups are granted/denied access to Software Sections, and that access is defined by the Permission Level.

For the sake of this discussion, let's define a few permission levels as: 'Full Access', 'Read only' and 'No Access'. This is essentially an enumeration of integer values, and is easily extended for your specific situation. I think it's helpful to arrange the permission values in some order of increasing/decreasing access level, so that code can for example make a menu entry visible if the permission level for the section the entry corresponds to is greater then or equal to 'x'.

So, we must have a table for Users, one for Groups, and one for Sections. It's interesting to contemplate whether a table should be created for the Permission level. This I think is a judgement call. You could simply "hard-wire" the values in your code, or you could make it a table and allow for more flexibility (and complexity) in their definition. I guess I would lean towards hard-wiring, since I wouldn't expect the values would change much if at all over the life of the application. Furthermore, the code that enables/disables menu entries, for example, needs to 'know' the valid permission levels, so your application will in effect hard-wire the values anyway.

The Users, Groups, and UserGroups tables we've already discussed. These tables define individual users and individual groups and the set of Groups each user is a member of.

We'll define a Software Sections table which lists the various sections that exist:

Sections
    SectionID
    SectionName
    <etc>

Now we must define the relationship that a user has to a software section. This relationship contains the User, the Section, and the permission level granted to this user in this section:

UserSections
    UserID
    SectionID
    Permission
    <etc>

The <etc> there could be for such things as an expiration date which defines the date when access is revoked. Thus, this table defines the relationship that a specific user has to a specific section and the permission level the user has for that section, plus any other data appropriate to describe or refine that relationship. No user data or Section data belongs in this table, only data relevant to the relationship itself.

A similar table would be created for GroupSections, defining the sections a group has access to and the associated permission level for that access.

I would query these tables by User and construct an in-memory structure that was a list of the sections the logged in user has access to and the associated permission level. This structure would be constructed by essentially created the UNION of the UserSections table and the GroupSections table for the given user. You'll need to decide how resolve conflicts when the user is explicitly given a certain level of access to a given section, but the user's membership in a group gives a different level. Take the minimum or maximum permission level of the two, whatever is appropriate for your situation.

As your application runs, it can query this in-memory structure for the software section in question, and assign access as indicated by the associated permission level.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
--------------------------------------------------------------------
CF_Girl:

Thank you for this help Joe. I have a very similiar application only I have a thousand users, and around 15 differnt sections they can choose from, however their permission level doesn't vary so they either have access to something or they don't. So my UserSections table would have hundreds of records with same userID and different sectionID for the various sections they have access to. I'm farely new to db design so my initial solution was to have user table that would contain a userid, and Sections_list which contained a list of all sectionsIDs that they had access to. And I would do the same thing for the groupPermissions. Is that violating db design?






Similar Threads
Thread Thread Starter Forum Replies Last Post
Invalid Object Name "Groups" Charlie05 VB Databases Basics 4 April 14th, 2007 10:54 AM
Active Directory - groups and permissions shanson ASP.NET 2.0 Basics 2 May 17th, 2006 03:20 PM
Groups Ma7T XSLT 1 August 25th, 2005 12:01 PM
Groups help in 8.5 for VS.net risu Crystal Reports 0 March 15th, 2004 04:27 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.