Re: Database design question
Date: Mon, 26 Jun 2006 20:18:41 GMT
> Dan 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
>>> doc_id int primary,
>>> doc_name varchar(50) not null
>>> user_id varchar(50) primary,
>>> user_name varchar(100) not null
>>> group_id int primary,
>>> group_name varchar(50) not null
>>> user_group_id int primary,
>>> group_id int not null,
>>> user_id varchar(50) not null
>>> 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.
>>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.
> > > 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?
> if you can share it. > > Thanks, > Sreedhar >Received on Mon Jun 26 2006 - 22:18:41 CEST