Re: Database design question

From: Bob Stearns <rstearns1241_at_charter.net>
Date: Mon, 26 Jun 2006 16:22:18 -0400
Message-ID: <3mXng.2396$vT.2326_at_fe02.lga>


sreedhardasi_at_gmail.com wrote:

> I have a database design questions. How can we specify one of two
> columns in a table should be not null? In other words, if one column is
>
> null then another column should not be null. Here is an example. Let's
> say we have document and we need to specify permissions to the
> document. The document has either individual or group level
> permissions.
>
> Document table
> doc_id int primary,
> doc_name varchar(50) not null
>
>
> User table
> user_id varchar(50) primary,
> user_name varchar(100) not null
>
>
> Group table
> group_id int primary,
> group_name varchar(50) not null
>
>
> UserGroup table
> user_group_id int primary,
> group_id int not null,
> user_id varchar(50) not null
>
>
> DocumentPermission table
> doc_id int,
> group_id int,
> user_id varchar(50)
>
>
> So, either group_id or user_id should not be null in the
> DocumentPermission table. Is there anyway we can have constraint like
> this on the table? Is creating another group for that user the only
> solution (Involves more administration)? Any help would be appreciated.
>
>
>
> Thanks,
> Sreedhar
>
This is a simple CHECK constraint:

alter table DocumentPermission

   add constraint SomeValidUser

       check (group_id is not null or user_id is not null)

Also you should have:

alter table DocumentPermission

   add constraint doc_id_fk

       foreign key (doc_id)
       references document(doc_id)

and similar statements for group_id and user_id.

You also need a primary key for DocumentPermission. Unless there is at most one user and/or one group permitted to each document, you will want all 3 _id columns to be part of the primary key, which means they must all be declared NOT NULL which, in turn, violates your design. You probably need 2 permission tables, DocumentUserPermission and DocumentGroupPermission. Received on Mon Jun 26 2006 - 22:22:18 CEST

Original text of this message