Re: Database design question

From: Jay Dee <ais01479_at_aeneas.net>
Date: Mon, 26 Jun 2006 20:18:41 GMT
Message-ID: <BiXng.884$vl5.235_at_tornado.ohiordc.rr.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?

What Bob said: design without NULLs.

In this case, I think "group permissions" and "user permissions" should be in separate tables. And I suspect you have too many attributes: is it really necessary, for example, to have an id distinct from a name? (Can there *really* be two groups/users with different id values and the same name? Your design allows it.)

What's up with the user_group_id attribute?

 From a different perspective: how is it that group_id and user_id docperm are functionally dependent on doc_id?

  I appreciate

> if you can share it.
> 
> Thanks,
> Sreedhar
> 
Received on Mon Jun 26 2006 - 22:18:41 CEST

Original text of this message