I want to allow users to share documents in a database (below they are called "items").
I have designed the following schema to allow for this (postgresql).
Here we go...questions at the bottom.
A lookup table for party types:
create table partyType(
id int not null primary key,
description text not null
);
insert into partyType values (1, 'Individual');
insert into partyType values (2, 'Organization');
A party is an individual or organization, such as a user or customer:
create table party(
id serial primary key,
type int not null,
name text not null,
foreign key (type) references partyType (id)
);
A party has many email addresses, and an email address can belong to multiple parties (such as "barb and jim jones"):
create table emailAddress(
id serial primary key,
address text not null
);
create table partyEmailAddress(
partyId int not null,
emailAddressId int not null,
primary key (partyId, emailAddressId),
foreign key (partyId) references party (id),
foreign key (emailAddressId) references emailAddress (id)
);
An item can be private or explicitly shared, or public but unlisted, or public and listed:
create table visibilityType(
id int not null primary key,
description text not null
);
insert into visibilityType values (1, 'Private / Explicit');
insert into visibilityType values (2, 'Public Unlisted');
insert into visibilityType values (3, 'Public Listed');
An item is the thing that you are sharing (this will be an actual type like Document later):
create table item(
id serial primary key,
ownerId int not null,
visibilityType int not null default 1,
onlyOwnerCanChangePermissions boolean not null default true,
foreign key (ownerId) references party (id),
foreign key (visibilityType) references visibilityType (id)
);
Got a more succinct name than "onlyOwnerCanChangePermissions" ? In gdocs, either only the owner can change permissions, or you can allow other editors to change permissions and add other users.
Someone with whom you share an item can be a viewer, commenter (can view too), or editor:
create table sharingRoleType(
id int not null primary key,
description text not null
);
insert into sharingRoleType values (1, 'Viewer');
insert into sharingRoleType values (2, 'Commenter');
insert into sharingRoleType values (3, 'Editor');
This allows an item to be shared with many, and for a party (via their email) to have many items shared with it.
create table itemShare(
itemId int not null,
emailAddressId int not null,
roleType int not null default 1,
primary key (itemId, emailAddressId),
foreign key (itemId) references item (id),
foreign key (emailAddressId) references address (id),
foreign key (roleType) references sharingRoleType (id)
);
How to prevent an item from being shared with its owner? (they already own it and don't need to share w themselves). I guess I could use a check constraint here.
There is a setting in gdocs "anyone with the link can x", which applies only when the item's visibility is public. How to implement this?