Database design question

Not sure if this could be handled using a constraint or not but you
could create a trigger to ROLLBACK the transaction (and raise an
error), if both the column values are null.
sreedhardasi@.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,
> SreedharHi
If you had a superset of Users and Groups you would not need two columns and
a foreign key constraint could be added. Why are is user_id varchar(50)?
John
"sreedhardasi@.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
>|||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|||Not sure if this could be handled using a constraint or not but you
could create a trigger to ROLLBACK the transaction (and raise an
error), if both the column values are null.
sreedhardasi@.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|||Hi
If you had a superset of Users and Groups you would not need two columns and
a foreign key constraint could be added. Why are is user_id varchar(50)?
John
"sreedhardasi@.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
>|||I guess your suggestion is that I have a superset (assuming both
group_id and user_id are same datatype) and a type column (specifies if
group or individual) in a table and reference this to UserPermission
table. Am I correct? I have user_id varchar(50) because I am storing
windows user names. I guess I just need to add another integer column
to User table and use it as primary. Thanks for your response.
John Bell wrote:[vbcol=seagreen]
> Hi
> If you had a superset of Users and Groups you would not need two columns a
nd
> a foreign key constraint could be added. Why are is user_id varchar(50)?
> John
> "sreedhardasi@.gmail.com" wrote:
>|||I guess your suggestion is that I have a superset (assuming both
group_id and user_id are same datatype) and a type column (specifies if
group or individual) in a table and reference this to UserPermission
table. Am I correct? I have user_id varchar(50) because I am storing
windows user names. I guess I just need to add another integer column
to User table and use it as primary. Thanks for your response.
John Bell wrote:[vbcol=seagreen]
> Hi
> If you had a superset of Users and Groups you would not need two columns a
nd
> a foreign key constraint could be added. Why are is user_id varchar(50)?
> John
> "sreedhardasi@.gmail.com" wrote:
>|||Hi
Yes you are correct! It should be alot simpler to use than your current
design and you can add a FK constraint, which you would only be able to do
with a CHECK constraint and function in the current design (which would be
slow!). If necessary you could create views that correspond to the current
users and groups tables.
John
"sreedhardasi@.gmail.com" wrote:

> I guess your suggestion is that I have a superset (assuming both
> group_id and user_id are same datatype) and a type column (specifies if
> group or individual) in a table and reference this to UserPermission
> table. Am I correct? I have user_id varchar(50) because I am storing
> windows user names. I guess I just need to add another integer column
> to User table and use it as primary. Thanks for your response.
> John Bell wrote:
>|||Hi
Yes you are correct! It should be alot simpler to use than your current
design and you can add a FK constraint, which you would only be able to do
with a CHECK constraint and function in the current design (which would be
slow!). If necessary you could create views that correspond to the current
users and groups tables.
John
"sreedhardasi@.gmail.com" wrote:

> I guess your suggestion is that I have a superset (assuming both
> group_id and user_id are same datatype) and a type column (specifies if
> group or individual) in a table and reference this to UserPermission
> table. Am I correct? I have user_id varchar(50) because I am storing
> windows user names. I guess I just need to add another integer column
> to User table and use it as primary. Thanks for your response.
> John Bell wrote:
>