Re: Nullable Foreign Keys?

Jesper wrote:

> I have 3 tables: TUsers, TUserGroups, TUserGroupMembers. Now I want to
> create a new table TPermissions which needs to contain information about
> what a user or a usergroup has permissions for in my application. To that
> end I'm thinking something like:
>
> TPermissions
> --------------
> nPermissionID
> nUserID
> nUserGroupID
> ...
>
> where the 2 last columns are foreign keys into TUsers and TUsergroups. The
> point being that each permissionitem must/can have *either* nUserID *or*
> nUserGroupID. Is this bad design? One of them will always be NULL. Is
> there a better way to implement something like this?
>
> Jesper.

Jesper, one simple approach is to declare that assignments can only be made to groups. This requires you to automagically generate a group for each user to avoid burdening the administrator, but it does make your own life much easier.

Also, I once saw a situation where the users and groups were put together into the same file (with a type column to distinguish), and permissions could be made to users or groups. This also worked well enough.

What both cases have in common is that your TPermissions table has only two foreign keys, the nPermissionID and the nUserGroupID, and the null problem goes away . This will make your life much easier, especially if your permissions are themselves hierarchical and you need to do permission resolution.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me