Best way to structure many-to-one allocation of users to projects in NoSQL

Question

I apologise if this is a bit of a stupid or broad question, but I'd like some general feedback please!

Say I have this DB structure for users (in Mongo/JS, but really it's all pseudocode- and the IDs would actually be DB references, not basic integers):

users = [
 {
 id: 1,
 name: 'John Smith'
 },
 {
 id: 2,
 name: 'Sheila X. Ample'
 }
]

... and I want to define some projects where one or more of these people can be collaborators on it. The project will define who has general access to it as well as who created it, who is the current primary collaborator (this will change regularly), who last updated it, etc etc.

Is this method best, where the IDs are represented directly onto the project object:

projects = [
 {
 id: 1,
 name: 'Project One',
 collaborators: [
 2
 ],
 primaryCollaborator: 2,
 lastUpdatedBy: 2,
 createdBy: 2
 },
 {
 id: 2,
 name: 'Another Project',
 collaborators: [
 1,
 2
 ],
 primaryCollaborator: 1,
 lastUpdatedBy: 2,
 createdBy: 1
 }
]
// Get the primary collaborator:
primaryID = projects.find({id: 2}).primaryCollaborator;

Or this one, where the properties are booleans on each individual collaborator:

projects = [
 {
 id: 1,
 name: 'Project One',
 collaborators: [
 {
 id: 2,
 primaryCollaborator: true,
 lastUpdatedBy: true,
 createdBy: true
 }
 ]
 },
 {
 id: 2,
 name: 'Another Project',
 collaborators: [
 {
 id: 1,
 primaryCollaborator: true,
 lastUpdatedBy: false,
 createdBy: true
 },
 {
 id: 2,
 primaryCollaborator: false,
 lastUpdatedBy: true,
 createdBy: false
 }
 ]
 }
]
// Get the primary collaborator:
primaryID = projects.find({id: 2}).collaborators.find({primaryCollaborator: true}).id;

As I said, I'm sure this kind of structural question has been asked a million times before but I wouldn't for the life of me know what to Google to find it!

What are the benefits or drawbacks of doing it in either way? Does it even make a difference? The first method would require that the IDs referenced on the project object existed and would cause a headache if, say, a collaborator was removed from the project. The second method seems a bit weighty and would cause a headache if something went awry and more than one collaborator was classed as being true as the primary collaborator, for example. Or is there a better method entirely?

Thanks in advance! :)

• user40980: On Stack Overflow: many to many relationship with nosql (mongodb and mongoose), Many-to-many relationship with NoSQL database - do these answer your question?
• user53019: What defines best? What works for you? Or doesn't? What's the likelihood of your application having the challenges with collaborators as you discussed?
• Philipp: By the way: Every NoSQL database is different. What works for MongoDB doesn't necessarily work in Couchbase, Neo4j, Cassandra or Redis because they all work completely different. So please don't believe that MongoDB is a typical NoSQL database, because there is no such thing.
• 9000: Just in case: I suppose this is not a centerpiece of your DB design, and there are other, bigger compelling reasons to use NoSQL as opposed to an RDBMS?
• mtj: Basically, you are designing a relational model. That is not what mongodb was made for (I cannot really tell regarding other nosql databases.) My recommendation is, that you either design a nosql-model (i.e. keep the data redundantly to ease the read operations, do not use ids, do not "join") or switch to a relational database.

Answer 1

I definitely recommend the second approach

where the properties are booleans on each individual collaborator

because this approach captures one of the important necessities why NoSQL (MongoDB) gained so much traction in the Agile movement.

Firstly, a big plus of this approach is syntactic sugar and easy flow of coding you get, consider:

# this example demonstrates approach-1
project = projects[i] # i.e. load projects form DB
primaryCollaboratorID = project.primaryCollaborator
primaryCollaborator = # again, DB/ORM specific code to get primaryCollaborator details
# now store more state to render a separate page for each member, OUCH!

compare this with

# this example demonstrates approach-2
a_collaborator = collaborators[i] # or whatever your DB says
if a_collaborator.primaryCollaborator: # note a_collaborator is already in memory
 prepareUI(a_collaborator, "send team message", "button")

Now, I am no expert programmer, and like @Phillip said every NoSQL DB is different so I do not claim either approach is faster/slower due to number of DB calls, but you can easily contemplate (foresee?) that the second approach would result in cleaner, more intuitive and easy to debug code.

Secondly, the second approach is (imho) more agile because it takes agility to a more atomic level. Why would you need it? Well

The project will define who has general access ... current primary collaborator (this will change regularly), who last updated it, etc etc.

So, tommorow if you have to have a new field qualityInspectorID for each project, your second approach will save you a lot of headache.

Answer 2

I came to the conclusion to use method B. This is because I realised that I needed to split my collaborators into different types of users and using method A, I wouldn't be able to join up the references as they would span across several documents. It's easier to do it with a bit of querying/scripting later than try and force it to work based purely on schemata.

• Christophe: I think this is a good approach only if the users are project dependent, and you don't track users across different projects, don't you ?