Re: Database design question

From: <kvnkrkptrck_at_gmail.com>
Date: 26 Jun 2006 13:05:17 -0700
Message-ID: <1151352317.683657.166160_at_p79g2000cwp.googlegroups.com>


sreedhardasi_at_gmail.com wrote:
> 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

> > > DocumentPermission table
> > > doc_id int,
> > > group_id int,
> > > user_id varchar(50)

DocumentUserPermission table
doc_id int (PK),
user_id int (PK)

DocumentGroupPermission table
doc_id int (PK),
group_id int (PK)

DocumentPermission (view) :=
SELECT doc_id, user_id, group_id
FROM DocumentUserPermission
FULL OUTER JOIN
DocumentGroupPermission USING (doc_id); Received on Mon Jun 26 2006 - 22:05:17 CEST

Original text of this message