Schema for Google Docs-like sharing

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 prima

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.

StackExchange Code Review Q#14093, answer score: 3