Re: Database design question

From: Dan <guntermann_at_verizon.net>
Date: 26 Jun 2006 11:23:40 -0700
Message-ID: <1151346220.392012.290710_at_y41g2000cwy.googlegroups.com>


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

For this model (I might have considered another), you might consider the following. See if this helps in solving your problem:

CREATE TABLE docperm
(

    doc_id INTEGER PRIMARY KEY

       CONSTRAINT fk_docid_docperm REFERENCES document (doc_id),     group_id INTEGER NULL

       CONSTRAINT fk_groupid_docperm REFERENCES group (group_id),     user_id VARCHAR2(50) NULL

       CONSTRAINT fk_userid_docperm REFERENCES user(user_id),     /* Table level constraint */
    CONSTRAINT ck_grouporuser_docperm

        CHECK (((group_id IS NULL) AND (user_id IS NOT NULL))
                 OR ((group_id IS NOT NULL) AND (user_id IS NULL)))
);

This proposal also makes the assumption that a mutually exclusive either-or condition is the desireable state. In other words, the constraint won't allow one to reference both a group id and a document id at the same time.

  • Dan
Received on Mon Jun 26 2006 - 20:23:40 CEST

Original text of this message