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! :)

Answer 1

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.

Answer 2

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.