Foreign keys in mongo?
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.
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 endclass Address include Mongoid::Document field :address field :city field :state field :postalCode embedded_in :studentendclass Score include Mongoid::Document belongs_to :course field :grade, type: Float embedded_in :studentendclass 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.
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 _id
s 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
- Suitable for one-to-few.
- Advantage: no need to perform additional queries to another document.
- 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: