Schema for Google Docs-like sharing

Question

I want to allow users to share documents (and other stuff) in a database-driven application.

I have designed the following schema to allow for this (PostgreSQL). Some of the tables like Party are skeletal for simplicity.

Diagram (minus lookup tables):

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');

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');

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,
  publicRoleType int not null default 1 comment 'the role, if the item is public',
  onlyOwnerCanChangePermissions boolean not null default true,
  
  foreign key (ownerId) references party (id),
  foreign key (visibilityType) references visibilityType (id)
  foreign key (publicRoleType) references sharingRoleType (id)
);

Do you have 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.

The below 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 emailAddress (id),
  foreign key (roleType) references sharingRoleType (id)
);

1. Should item.ownerId be removed from item and added as a role type, and be added as an "automatic" itemShare? It would be easier to query for all docs that a user has access to this way.

2. Is item.publicRoleType correct, seeing as it's only used if the item is public? It seems somewhat denormalized. I could move visibilityType to itemShare, get rid of publicRoleType, and make email addy nullable on itemShare.

Alternative schema - improved?

_{(source: snag.gy)}

Answer 1

I would definitely rename onlyOwnerCanChangePermissions, maybe to permissive or restrictPermissions or something like that.

I would also definitely remove item.ownerId, create a new RoleType owner and propagate an itemShare entry. I also think that itemShare is a rather bad name and should be renamed to something like permission or accessList.

Get rid of item.publicRoleType and use itemShare instead. This could also simplify your code e.g. you only have to check itemShare for the access. But i would use an AnonymousUser instead of making the emailAddresse nullable.

I think a better name for sharingRoletype would be just roleType as a role is not necessarily related to sharing.

item.visibilityType doesn't really fit into itemShare as itemShare defines permissions (what and who could do what with the document) for users. But you could create a SystemUser and merge visibilityType and sharingRoleType but this isn't a good fit either.