-1

I'm coming at this problem with a RDMS background so some of the best practices of document databases is new to me. I'm trying to understand the best way to store shared data and access rights to that data. The schema in SQL Server might look like this:

Project Table

projectId PK
ownerId FK User.userId
title
...

User Table

userId PK
name
...

ProjectShare Table

sharedById FK User.userId
sharedWithId FK User.userId
state
...

With the above tables I could query all projects that a user has access to. I could then query for all the data related to each project. Each project will have many related tables. The hierarchical nature of the data seems well suited for a document database.

How would I best structure something like this in a document database like MongoDB, CouchDB or DocumentDB?

user1843640
  • 3,613
  • 3
  • 31
  • 44
  • 2
    Honestly, there's no right answer to this. You might end up with a similar approach. You might end up embedding shared projects as subdocuments (but that creates an *unbounded array* condition). You might choose to keep all content in a single collection (for transaction purposes), or create multiple collections. Also: This really isn't a DocumentDB question - this is a document database modeling question (as the same basic considerations will be made for other doc databases too, with the exception of # of collections). – David Makogon Oct 27 '16 at 14:33

1 Answers1

0

There are indeed multiple approaches to model this data in DocumentDB.

Collections in DocumentDB can host heterogeneous set of documents and can be partitioned for massive scale.

Depending on the query requirements, data could be denormalized in many directions - either by pivoting on project (and keeping all users associated including owners, shared by and sharedWith details) or by pivoting on users (and keeping all the projects they own, the details of the projects including information of other users who shared this project etc).

One can also control the level of denormalization by simply storing a soft reference and keeping the referred information as a separate document. For instance, if we pivot by project, we could store all of user information repeatedly in each project document or just store userId alone (in which case user information is stored in a separate document). We can control how much referred data to store based on your query/ logical integrity constraints.

David Makogon
  • 69,407
  • 21
  • 141
  • 189
Shireesh Thota
  • 283
  • 1
  • 1
  • FWIW - the Azure DocumentDB team would be happy to help provide 1:1 guidance on data modeling to fit your particular scenario. Please feel free to ping them at askdocdb {at} microsoft.com – Andrew Liu Nov 02 '16 at 03:29