Mysql – MongoDB/MySQL – auto increment vs object ID

mongodbMySQLPHPrelational-theory

I am in the process of evaluating MongoDB and writing some basic PHP applications around it. Typically in my PHP/MySQL applications, I would create an ID field and set it to auto-increment on each row insert. I would then use this ID to relate to/join tables together.

However in MongoDB, there is no auto-increment (unless you create function to do it, I believe), only an objectID. Because of this, i'm unsure of how to relate collections together.

Is it worth writing an auto-increment function (as explained here: http://docs.mongodb.org/manual/tutorial/create-an-auto-incrementing-field/), or is it common practice to store the ObjectID?

An example would be the following MySQL query:

SELECT * FROM cars WHERE car_type = '2' 

But in MongoDB….

db.cars.find( { car_type : 507c35dd8fada716c89d0013 } );

Is the above standard practice? Am I using Mongo incorrectly by creating an auto-increment field?

Darius

EDIT: I think I am so deeply rooted in MySQL that I can't imagine an application without auto-incrementing IDs. Is it safe to store objectIDs (just as you would store a MySQL AI ID)? Is this bad for readability, since you would have to look up that objectID just to see what it relates to?

Best Answer

In MySQL, or almost any other RDBMS, it is common to have an ID for the row (object), so you use it in other tables to make a link between the tables (Relations, or PK and FK if you prefer). This is part of the normalization process that is recommended for most types of DB design [there are exceptions though].

In MongoDB however, the general way or design is to denormalize. i.e. the document (row) is stored with all its related objects.

To make it more clear:

In MySQL you would have two tables: cars and car_types. In cars you only store the ID of the type of this car.

In Mongo DB, you would have one collection called cars, and the document in this collection would have a sub-document that contains the type's info. (You still can have a separate collection for types, but you don't store only objectID in car's collection)

HTH