Re: Database design question
Date: 26 Jun 2006 13:08:02 -0700
Message-ID: <1151352482.861381.30390_at_i40g2000cwc.googlegroups.com>
kvnkrkpt..._at_gmail.com wrote:
> 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
> >> > > > group_id int,
> > > > DocumentPermission table
> > > > doc_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);
LOL - wrote that view in a hurry... I meant something more along the lines of:
DocumentPermission (view) :=
SELECT doc_id, user_id, NULL group_id
FROM DocumentUserPermission
UNION
SELECT doc_id, NULL user_id, group_id
FROM DocumentGroupPermission
Received on Mon Jun 26 2006 - 22:08:02 CEST