Modeling Many-to-Many Relationships in Postgresql

Question

I’m using Postgresql.

I need some help with the best way to design my database.

At it’s core, my webapp has an entity which I refer to as a collection of items - think “10 Must Read Books on Space Travel”. Here the item is a ‘book on space’ travel, and a collection is a list of 10 such items, in this case books. So, I have the following tables:

**Collections**
Id
Name
itemId
Author (need help on this field)

**Items**
Id
Name
Description
<Other Fields>

**Users**
Id
Name
Email
<Other fields>

Problem 1: A user can login and start a new collection. This user is the owner of this collection. However, he can invite/allow other users to collaborate on this list, which means that other users can add items to it. This means that the collection now has multiple users working on it. What’s the best way to model this? How do I add multiple users for the collection (probably with different roles).

One way I though was thinking was to keep the author separate and setup a collaborators field with many to many relationship. Is it a good idea? Any other ideas?

Problem 2: The normal users viewing the collection can upvote items in the collection and the order of the items in the collection is determined by the number of upvotes. What’s the best way to store the upvotes?

Answer 1

1. Each collection has one author, so having an author column in the collection table is perfect.
2. Each collection can have multiple items, though, which is an m:n relation.
3. Then you want other users allowed to add items to a collection. One user can be invited to many collections, one collection can have many invitees. An m:n relation. This also means that we should store the contributing user with the item added to the collection.
4. Then you want to store upvotes. A user can upvote many collection items and a collection item can be upvoted by many users. m:n again.

We end up with these tables:

• User: id, name, email, ...
• Item: id, name, description, ...
• Collection: id, name, author_user_id
• Collection_Item: id, collection_id, item_id, user_id
• Invitee: collection_id, invitee_user_id
• Upvote: collection_item_id, user_id

With this data model you can check whether a user has been invited to a collection and only then allow them to ad an item. If you wanted to have this ensured by the DBMS though, you'd have to change "Invitee" to "Contributor" and add the author themselves to this table. Then change Upvote.user_id to Upvote.contributor_id and add a foreign key from "Collection_Item" to "Contributor".

Answer 2

Since you're using Postgres, you want to normalize your tables. What normalization means, in a sentence, is that your columns are represented by the key, the whole key, and nothing but the key. Normalization can be a three-step process, but I'm just going to show the final result.

Generally, in a database, table names are singular. User, Collection, Item. Also, it's less confusing to name your ID fields with the table name. It makes join SQL easier to read.

So let's start with the User table.

User
----
User ID
User Name
User Email
...

So far, so good. All of the columns have to do with a user.

Next, let's look at Collection

Collection
----------
Collection ID
Collection Name
Owner ID

So far, so good. The Owner ID is the User ID of the owner of the collection.

Next, let's look at Item.

Item
----
Item ID
Item Name
Item Description
...

So far, so good. All of the columns have to do with an item.

Now, let's look at the relationship between Collection and Item you described in problem 1. A Collection can have one or more items. An Item can be in one or more collections.

When you have a many-to-many relationship, you create a junction table. So let's create a CollectionItem junction table.

CollectionItem
--------------
CollectionItem ID
Collection ID
Item ID
CollectionItem timestamp
CollectionItem contributor ID

Where CollectionItem ID is the primary clustering key. You also have a unique index on (Collection ID, Item ID), so you can pull the collection together. You can also have a unique index on (Item ID, Collection ID), so you can see which items are in multiple collections.

You also have a unique index on (Collection ID, CollectionItem contributor ID). This allows you to see which contributor (user) contributed the item to the collection. This user ID could be the owner ID from the collections row or a different contributor.

Looking at problem 2, we need a Vote table. The Vote table is another junction table connecting a collection, item, and voter (user).

Vote
----
Vote ID
Collection ID
Item ID
Voter ID
Vote timestamp

Where Vote ID is the primary clustering key, and you have a unique index on (Collection ID, Item ID, Voter ID). You may also have two other unique indexes, depending on whether or not you want to group the votes by voter or item.

Edited to add:

You also need some kind of Permission table.

Permission
----------
Permission ID
Owner ID
Contributor ID

Where Permission ID is the primary clustering key and you have a unique index on (Owner ID, Contributor ID). You can also have a Permission Type, which you haven't defined, so I can't add it to the Permission table. The Permission Type would also be a part of the unique index.

I hope this explanation has been helpful.