Re: Database design question
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