MongoDB - which way round should I make a relationship?

Question

I am currently experimenting with Node.js and MongoDB to build an app for my wedding. I am currently working on a table planner for the wedding breakfast which will work like a Trello Board. Each guest will be an item and each dinner table would act like a trello list. So when built we can just drag and drop guests from an unassigned pile to a particular trello style board which will allocate them a position at the table.

Each Breakfast table will seat multiple guests and a guest can only sit at one table. I'm getting a bit confused whether to include a 'table' as a foreign key in the guest model or vice versa.

Having the 'table' as the foreign key allows me to easily query for guests which do not currently have a table. On the other hand, having guests as foreign keys in the 'table' model lets me easily query for tables which don't yet have any guests assigned yet.

I will need to perform both of the above queries in order to update the state in React.

Below shows my Guest schema and its relationship to the table model:

const guestSchema = new mongoose.Schema({
  firstname: {
      type: String,
      required: 'Please provide the first name of the guest',
      trim: true
  },
  surname: {
      type: String,
      required: 'Please provide the surname of the guest',
      trim: true
  },
  attending: {
      type: String,
      default: 'Awaiting RSVP'
  },
  menu: {
      type: String,
      default: 'Awaiting RSVP'
  },
  allergies: {
      type: String,
      default: 'Awaiting RSVP'
  },
  table: {
      type: mongoose.Schema.ObjectId,
      ref: 'Table',
  }
});

const tableSchema = new mongoose.Schema({
  name: {
    type: String,
    required: 'Please provide the name of the table',
    trim: true
  },
  capacity: {
    type: Number,
    required: 'Please provide the capacity of the table',
  }
});

This really is far too general and opinion based. There really is no "best practice way" and only "what works best for your usage pattern". And frankly if you "choose MongoDB", then your **first** point of focus should be on "embedding" instead of creating relationships just like an RDBMS does. *"Horses for Courses"* as the saying goes. — Neil Lunn, Apr 28 '18 at 01:52
See: [MongoDB relationships: embed or reference?](https://stackoverflow.com/q/5373198/2313887) and [Mongoose populate vs object nesting](https://stackoverflow.com/q/24096546/2313887) which at least attempt a un-biased introduction of facts. — Neil Lunn, Apr 28 '18 at 01:54

HarryH · Answer 1 · 2018-04-27T15:53:50.290

It is just a different approach since you are trying to use a document model as your main object and multiple collections for varying types. It also sounds like a table can only ever have so many people, and each person can only ever be on a table.

A table can have many people, but has a unique person per table

A person can only be part of one table.

I personally do not think for YOUR use case that it is a problem to have a reference in both the table and the person. It would be silly to try to create a bridge table and NoSQL does not necessarily conform to the same normalization rules for SQL databases. That being said, larger scale applications might vary. I think the problem people have in general is that everyone wants to shove high level architecture and complex theory in use cases that will not really benefit from it. Play with and see what works and just do some research at how your solution may or may not scale in an much larger/complex data set.

Even if you had 1000 people at your wedding, I doubt this would be an issue. As a heads up, just for general practice sake, it is good to think about what kind of database you need to use for your type of data. If you have a lot of relational data, MongoDB can be done in that way, but it may drop in performance compared to a traditional SQL DB from research and work I have done in the past. This stuff should be thought out prior to picking your technology.

Since you are using Mongoose, you can reference other objects via ObjectId easily and the queries should be straight forward.

If you slap it on the user, you can query a table by the table ref on the Users collection. It does not have to be both ways and since your user can only ever have one table, it does not seem necessary to do both.

Think of it this way. For example, you may have a table with 10 users. When you are looking at the user, you want to find out what table he/she is at. That means when you query the tables collection, you have to query each record which may have another array of users. The lookup is probably a lot slower than the other way around which is looking at a single property on each user.

Just wanted to offer insight into the different ways you can do it. I will not say what is the best because there might be other variables I am not accounting for. However, I would probably just put it on the User themselves. Figure out what works best for you.

Thanks for the answer. The main reason I picked MongoDb is it is often used with Node and React which I am also using for the project but yes I take your point it might not be the best suited. From your answer, the part I don't yet understand is how do I do a query for tables without guests if I have the tableid as a field in my Guest model — James Howell, Apr 27 '18 at 15:53
You would have to query your users collection where the tableId matches the table you are looking at. So like I said, I don't know what your views will look like. If you have to have all the tables show up, it might be better to have both on their. You can query the tables by their users as you need and vice versa. — HarryH, Apr 27 '18 at 15:55
If you look at the mongoose docs, they even have this relationship in an example.http://mongoosejs.com/docs/populate.html — HarryH, Apr 27 '18 at 15:57
I already query the users collection for users without a table with: const guests = await Guest.find({ table: { $exists: false } }); But I need to show all the tables regardless of whether they have guests. And the tables which have guests should be pre-populated with those guest names. hence why I need the relationship to work both ways. Can I set up refs in both directions as I have done above in my Guest collection? — James Howell, Apr 27 '18 at 15:59

score 0 · Answer 2 · answered Apr 27 '18 at 19:40

I would store the table number on the person, though I don't know that you'd run into any problems going the other direction.

When designing a database, children should (generally) have reference to their parents, not the other way around. In my mind, a table 'owns' the people who sit at the table (one-to-many relationship), rather than people owning a portion of the table, making the table the parent.

Another benefit would be a single point of update if someone moves tables. You would simply update the guest record, rather than removing from one table and adding to another table.

What typically helps me in modeling decisions is to look at the problem on a grander scale. Imagine creating a database of where people live. You would want to have people who have a street address property and a field that links to a state record that has a field that links to a country record. Once you generalize and scale up your problem, there's usually a more clear solution.

If you go this approach, you can use aggregation to determine which tables are not full.

db.guests.aggregate([ {$group : { _id: '$tableId', seatsFilled : {$sum : 1}}} ])

MongoDB - which way round should I make a relationship?

2 Answers2