Foreign keys in mongo?

Question

How do I design a scheme such this in MongoDB? I think there are no foreign keys!

Answer 1

How to design table like this in mongodb?

First, to clarify some naming conventions. MongoDB uses collections instead of tables.

I think there are no foreign keys!

Take the following model:

student
{ 
  _id: ObjectId(...),
  name: 'Jane',
  courses: [
    { course: 'bio101', mark: 85 },
    { course: 'chem101', mark: 89 }
  ]
}

course
{
  _id: 'bio101',
  name: 'Biology 101',
  description: 'Introduction to biology'
}

Clearly Jane's course list points to some specific courses. The database does not apply any constraints to the system (i.e.: foreign key constraints), so there are no "cascading deletes" or "cascading updates". However, the database does contain the correct information.

In addition, MongoDB has a DBRef standard that helps standardize the creation of these references. In fact, if you take a look at that link, it has a similar example.

How can I solve this task?

To be clear, MongoDB is not relational. There is no standard "normal form". You should model your database appropriate to the data you store and the queries you intend to run.

Answer 2

We can define the so-called foreign key in MongoDB. However, we need to maintain the data integrity BY OURSELVES. For example,

student
{ 
  _id: ObjectId(...),
  name: 'Jane',
  courses: ['bio101', 'bio102']   // <= ids of the courses
}

course
{
  _id: 'bio101',
  name: 'Biology 101',
  description: 'Introduction to biology'
}

The courses field contains _ids of courses. It is easy to define a one-to-many relationship. However, if we want to retrieve the course names of student Jane, we need to perform another operation to retrieve the course document via _id.

If the course bio101 is removed, we need to perform another operation to update the courses field in the student document.

More: MongoDB Schema Design

The document-typed nature of MongoDB supports flexible ways to define relationships. To define a one-to-many relationship:

Embedded document

1. Suitable for one-to-few.
2. Advantage: no need to perform additional queries to another document.
3. Disadvantage: cannot manage the entity of embedded documents individually.

Example:

student
{
  name: 'Kate Monster',
  addresses : [
     { street: '123 Sesame St', city: 'Anytown', cc: 'USA' },
     { street: '123 Avenue Q', city: 'New York', cc: 'USA' }
  ]
}

Child referencing

Like the student/course example above.

Parent referencing

Suitable for one-to-squillions, such as log messages.

host
{
    _id : ObjectID('AAAB'),
    name : 'goofy.example.com',
    ipaddr : '127.66.66.66'
}

logmsg
{
    time : ISODate("2014-03-28T09:42:41.382Z"),
    message : 'cpu is on fire!',
    host: ObjectID('AAAB')       // Reference to the Host document
}

Virtually, a host is the parent of a logmsg. Referencing to the host id saves much space given that the log messages are squillions.

References:

1. 6 Rules of Thumb for MongoDB Schema Design: Part 1
2. 6 Rules of Thumb for MongoDB Schema Design: Part 2
3. 6 Rules of Thumb for MongoDB Schema Design: Part 3
4. Model One-to-Many Relationships with Document References

Answer 3

You may be interested in using a ORM like Mongoid or MongoMapper.

http://mongoid.org/docs/relations/referenced/1-n.html

In a NoSQL database like MongoDB there are not 'tables' but collections. Documents are grouped inside Collections. You can have any kind of document – with any kind of data – in a single collection. Basically, in a NoSQL database it is up to you to decide how to organise the data and its relations, if there are any.

What Mongoid and MongoMapper do is to provide you with convenient methods to set up relations quite easily. Check out the link I gave you and ask any thing.

Edit:

In mongoid you will write your scheme like this:

class Student
  include Mongoid::Document

    field :name
    embeds_many :addresses
    embeds_many :scores    
end

class Address
  include Mongoid::Document

    field :address
    field :city
    field :state
    field :postalCode
    embedded_in :student
end

class Score
  include Mongoid::Document

    belongs_to :course
    field :grade, type: Float
    embedded_in :student
end


class Course
  include Mongoid::Document

  field :name
  has_many :scores  
end

Edit:

> db.foo.insert({group:"phones"})
> db.foo.find()                  
{ "_id" : ObjectId("4df6539ae90592692ccc9940"), "group" : "phones" }
{ "_id" : ObjectId("4df6540fe90592692ccc9941"), "group" : "phones" }
>db.foo.find({'_id':ObjectId("4df6539ae90592692ccc9940")}) 
{ "_id" : ObjectId("4df6539ae90592692ccc9940"), "group" : "phones" }

You can use that ObjectId in order to do relations between documents.

Answer 4

From The Little MongoDB Book

Yet another alternative to using joins is to denormalize your data. Historically, denormalization was reserved for performance-sensitive code, or when data should be snapshotted (like in an audit log). However, with the ever- growing popularity of NoSQL, many of which don’t have joins, denormalization as part of normal modeling is becoming increasingly common. This doesn’t mean you should duplicate every piece of information in every document. However, rather than letting fear of duplicate data drive your design decisions, consider modeling your data based on what information belongs to what document.

So,

student
{ 
    _id: ObjectId(...),
    name: 'Jane',
    courses: [
    { 
        name: 'Biology 101', 
        mark: 85, 
        id:bio101 
    },
  ]
}

If its a RESTful API data, replace the course id with a GET link to the course resource

Answer 5

Short answer: You should to use "weak references" between collections, using ObjectId properties:

References store the relationships between data by including links or references from one document to another. Applications can resolve these references to access the related data. Broadly, these are normalized data models.

https://docs.mongodb.com/manual/core/data-modeling-introduction/#references

This will of course not check any referential integrity. You need to handle "dead links" on your side (application level).

Answer 6

The purpose of ForeignKey is to prevent the creation of data if the field value does not match its ForeignKey. To accomplish this in MongoDB, we use Schema middlewares that ensure the data consistency.

Please have a look at the documentation. https://mongoosejs.com/docs/middleware.html#pre

Answer 7

I see no reason why you cannot use a NOSQL db as a relational DB since you have aggregate pipelines which you can use to create views like SQL inner joins.

Each collection should contain one type e.g. order and order-details is a 1:M relation.

ORDER COLLECTION
id (PK) Type: GUID
OrderNo (candidate or alternate PK)
OrderDate
Customerid (FK)
...more order fields

ORDER-DETAILS COLLECTION
id (PK)
Orderid (FK)
Productid (FK)
Qty
...more fields

PRODUCT
id (PK)
Supplierid (FK)
Name
Cost

And so on.

The reason why you would not simply use a relational database is because a NOSQL database has other features you could use in tandem with relationally designed data collections.

The way you implement the relations is the same as in relational databases. You have to add the foreign keys yourself (primary keys are added automatically for each document object) and create the normalized join collections. A database will not normalize itself usually.

The only caveat is that you have to code referential integrity yourself as NOSQL does not do that (yet). So in your admin UI if a user tries to delete a parent that has children you throw a friendly error saying they have to delete the children first. It is not hard but you still need to be careful when using DB utilities not to delete a parent that has children because the DB will let you.

Always use the naming conventions for foreign keys id. For example:

ORDER-DETAIL COLLECTION
id (PK)
Orderid (FK) .... you an easily deduce that this foreign key is the primary key in the Order collection by sticking to this naming convention.