Re: Database design question

Dan wrote:
> 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

Thanks Dan. I think this solution works, I will try to implement in this way. Do you think of any alternative model to this? I appreciate if you can share it.

Thanks,
Sreedhar Received on Mon Jun 26 2006 - 21:04:15 CEST