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 24th, 2004, 12:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default Users, Groups and Permissions

I need to implement some sort of user permissions to my software. The idea is very similar to the way NT permissions work, you have users and groups, users can be members of groups, both users and groups can have permissions and any users that are a member of that group inherit the permissions.

Anyway, my problem is with my database table design. I think I need 4 tables. One would list all the users, another all the groups, the other would list all the permissions and the last one would link the users to the groups.

The permission table would contain the GroupID or the UserID (if there was a GroupID then UserID would be Null and vice versa), the permission the row is referring to and the value of that permission (e.g. Read/Add/Edit etc.). The structure would be like this:
Code:
UserID      int Null
GroupID     int Null
Permission  int Not Null
Value       int Not Null
How can I be sure that for each user or group there is only one row for each permission? For example let's say that I have 2 permission constants, 1 and 2, then I would not want to get into the situation where permission 1 could appear twice for the UserID 1:
Code:
UserID  GroupID Permission  Value
------  ------- ----------  -----
1       NULL    1           1
NULL    1       2           1
1       NULL    1           2     This is the problem row
I can not have a unique index on the UserID and Permission columns and another unique index on the GroupID and Permission columns because there could be several Null values in each. Another option is to have one unique index on the UserID, GroupID and Permission columns, but is this the best database design?

If anyone has got any better ideas then please can you let me know.

Regards
Owain Williams
__________________
Regards
Owain Williams
 
Old February 24th, 2004, 12:36 PM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Would it help if you had one column, EntityId to represent either group or user and a second column, EntityType to represent either user or group? You can then have a unique contraint spanning EntityId, EntityType and Permission.

--

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

I think you need 6 tables:

    Users
        UserID (PK)
        etc.

    Groups
        GroupID (PK)
        etc.

    Permissions
        Permission (PK)
        Value
        etc.

These three are the base tables defining the distinct Users, Groups, and Permission values.

Then you need tables to define the relationships (many-to-many) between these base tables:

    UserGroups
        UserID (PK)
        GroupID (PK)

    UserPermissions
        UserID (PK)
        Permission (PK)

    GroupPermissions
        GroupID (PK)
        Permission (PK)

with, of course, the appropriate referential integrity constraints between the base tables and these relationship tables.

The various (composite) primary key constraints on these relationship tables insure that no user is a member of the same group more than once, nor has the same permission more than once, nor that a group has the same permission more than once. The referential constraints insure that everybody is associated with only valid entries in the other tables.

The set of permissions for a given user, then, is the "union" of the set of permissions associated with that user via the UserPermissions table with the set of permissions associated with each group (or a selected group) that the user is related to via the UserGroups table.

(No NULLS anywhere ;)


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old February 24th, 2004, 12:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your contribution Joe, I hadn't thought of that one. It does appear to be a better solution.

Regards
Owain Williams
 
Old February 24th, 2004, 01:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff, do you mean that the Permission column in the Permissions base table would be an identity value, then the other rows would identify the permission constant and the permission value for that constant? Because some groups and users will need different levels of access to certain areas of the software.

I am using the permission word to identify a section of software and the value word to identify the level of access. Therefore I think that the table structure I will need would be:
Code:
PermissionID    int identity Not Null (PK)
Permission      int          Not Null
Value           int          Not Null
Perhaps my terminology is screwed up. This way a user or group points to a PermissionID, then Permission is the constant identifying the section of software and value is the level of access that user or group is entitled to.

Have I understood you correctly?

It is a nice idea, however I am using a poo database engine so I think that Joe's idea is going to produce less overheads.

Regards
Owain Williams
 
Old February 24th, 2004, 02:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.

Without getting into the "Whither identity columns as primary key" debate, if you feel more comfortable using a 'PermissionID' as the primary key, go ahead. The idea is that you have a set of permissions and the value associated with each permission. Somehow you must be able to distinguish one permission/value pair from another. Using an ID column is one way to do this. There are others.

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?).

But it will do in a pinch. If your database engine is no good at doing JOINs, it may be OK to avoid a fully normalized database design, since such a thing generally increases the number of JOINs which must be done. Just be sure that's the reason why you are doing it. While normalization tends to increase JOINs, it also decreases data redundancy and the update/query anomalies associated with that redundant and/or heterogenous data.

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old February 24th, 2004, 05:26 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Jeff,

With regards to your comments about having a row with a type identifier, I'm hoping you can clarify something keeping in mind that I'm a far cry from a database design expert:

Let's say I have the following tables:
Code:
Student    Teacher
-------    -------
StudentID  TeacherID
FirstName  FirstName
LastName   LastName
Doesn't this break a rule of normalization? Isn't there one about storing the same kind of data in multiple places? It seems like it would be more "normal" to store it like this:
Code:
Person
-------
PersonID
PersonType
FirstName
LastName
However, you are saying that this breaks the rule about multiple entities. From a layer of abstraction higher, aren't they the same? They are Person entities, but there is just a bit of additional data to sub class them. I have this exact scenario where I have tables for students, parents, teachers and some more. I had planned on rebuilding the structure to have a single User table with typing on the records and additional tables to hold the more entity specific data (i.e. student details that don't apply to teachers, etc.)

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old February 24th, 2004, 06:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

First off, let me state that database design can sometimes be more an art than a science. ;) Database normalization is a set of "rules" which attempt to measure a design against a set of conditions which should exist to guarantee that queries against the data and updates to the data do not result in redundancies which can lead to either inconsistent results or worse, to anomalies during updates (data that should be updated isn't, or is updated inconsistently).

That having been said, though, it does not follow that a fully normalized database is (necessarily) a good design, nor that a well designed database is fully normalized.

It's the old "it depends" caveat. It depends on the needs of the application(s) which make use of the data, and a design frequently is a compromise between sometimes conflicting goals of performance versus ease of querying and maintaining the data.

As to your example, I don't see that two tables, one of Students and one of Teachers in and of themselves break any normalization rules. A lot depends on what is being modeled by the design. If I am modeling the courses offered and taken in a school, then I think I indeed have two completely distinct entities: Students and Teachers. Presumably I would have some structures which may directly or indirectly describe relationships between them (e.g. a class roster), but in the end, Students are students and Teachers are teachers and they aren't the same thing at all - they don't share many (or any) attributes, and that makes them distinct and quite different entities.

On the other hand, if I were modeling the population of the school as a whole, it may be sufficient to consider the entities as 'people' (or 'employees', though students are kind of a bad example of that) with perhaps an 'occupation' attribute. Here, this occupation attribute allows me to distinguish one element of the population from another in the cases where I need to, but the emphasis in this model would be on their 'sameness' rather than their differences.

Thus either approach could work, depending on how the entities are modeled in the application: as generic 'people' with attributes such as occupation, or distinct entities which are related to one another in various ways. The question is, are students and teachers more often lumped together or considered apart and distinct? The answer to that will help drive you to a design.

Trouble is, it's easy to convince yourself that *everything* is an attribute, then you end up with some generic monstrosity like an attributes table consisting of an attribute type identifier and value, and all your data from everything ends up in this table and you can't query or maintain anything reasonably.

People trying to map objects into a relational database often fall into this trap.

There's no one right answer. In another thread elsewhere somebody has asked a question about how to estimate the time it takes to do a database design. I've avoided addressing that thread because I don't know how to answer the question with anything other than "it depends", and "there is probably more than one way to do it, so there is more than one answer". Which won't help the original poster.





Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old February 24th, 2004, 11:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

About the other Thread...

Jeff, I am one among those who are really looking for an answer to that time estimation thread. Quite interesting it is. I really wanted to know if there is a defined rule or set of work flow to be followed to find answer for that thread. I would suggest you to put in your thoughts there. May be, that helps him/many in a way to put them on right path to their destination. Who knows? Your reply could actually answer his/others needs in knowing the answer to that thread, though there is nothing defined for it as you said "it depends". [:-)]

Thanks,

-Vijay G
 
Old February 25th, 2004, 12:57 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Re: Database design time

How much more of a relative question can you get?! I would venture that the actual design time of any database system would vary greatly based purely on the simple variable of one's own skills. I know my way around SQL, but I'm a far cry from a DB designer. I would imagine that Jeff is a healthy bit more skilled at such tasks than I am so the design time for the same project would be drastically different depending on who you asked to design it.

Peter
------------------------------------------------------
Work smarter, not harder.





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.