MongoDB Manual References vs DBREF

[DDavisLGH]

Hello all,

I've been researching MongoDB and trying to see if it would be a fit for my company's product catalog.

I came across database references: http://docs.mongodb.org/manual/reference/database-references/ and had some questions about how exactly to link documents together.

Assume we have two db.categories and db.products collections... I have documents in db.products that need to link to their categories in db.categories.

I have a "category" field in db.products that I will store the objectID of the category document in. However, I do not see a way to specify that this objectID references the db.categories collection. Do I need to use DBREFs?

Below is a brief example of my data...

Document in db.categories:

 {
        "_id" : ObjectId("546b72a47ea1d03593000158"),
        "name" : "Category1",
        "type" : "type1 of category1",
        "spec_columns" : [ 
            {
                "field" : "capacity",
                "display" : "Capacity (tons)"
            }, 
            {
                "field" : "weight",
                "display" : "Weight (lbs)"
            }
        ]
    }

Document in db.products:

{
    "_id" : ObjectId("546a575f7ea1d03593000154"),
    "model" : "NER003H",
    "category" : ObjectId("546b72a47ea1d03593000158"),
    "capacity" : "1/2",
    "weight" : 10
}

Any help, suggestions and advice is welcome and greatly appreciated.

Darius

[Will Berkeley]

Joins in MongoDB are always application-side. DBRefs are a client-side concept and exist as a convenience; on the server they reduce to storing the _id and namespace of the referenced document as opposed to just its _id. Some drivers have helpers to resolve DBRefs into the referenced document, but (at least for official MongoDB drivers) will not resolve them automatically. Generally, an _id value is simpler and sufficient because it's known (in application logic) which collection the _id is referencing. For your case, if you do want to reference the category document from the product, an _id is all that's needed. However, this isn't the only option. For example, instead of referencing categories from products, you could embed a category document in each product or embed important category information in each product with a reference to the full category. The optimal way to model the relationship between products and categories depends on exactly how you are querying this information. I'll link you to some helpful resources:

1. Model Relationships Between Documents - basic documentation about modeling 1-to-N relationships in MongoDB, for N >= 1

2. Modeling tree structures - helpful if categories are hierarchical

3. 6 Rules of Thumb for MongoDB Schema Design - a great blog post that goes in to more depth about the possibilities for modeling relationships in MongoDB than #1

I'm also happy to talk more specifically about what could be a good design for your use case if you can give more detail about how categories and products relate and what kinds of queries you plan to run most frequently.

-Will

[DDavisLGH]

Hi Will,

I am not certain if my previous message went through or not, as I do not see it. I would like to firstly thank you for the resources and information - it has been extremely helpful in my learning. I am going to make this a short one and hopefully my last message went through.

Image of the HTML table we're trying to build.

Screenshot of my two "products" documents.

Screenshot of my "category" document  (the complete category document is here)

We are using PHP. The queries ran against the database will be basic (sorts, limits, distinct, etc). I need the documents in "products" to relate back to their category document, for the purpose of table hierarchy and the display text. For example, if we wanted to find all Minifors with a direct speed of >20 FPM, I'm assuming the following would work:

db.products.find({speed_direct_fpm:{$gt:"20"}, category: "546ce5087ea1d0359300015c"});

Do you know of a better way to design our data so that we can build up the table, like in the first screenshot link?

Any help would be greatly, greatly appreciated. 

My PHP attempt:

// $category['spec_columns'] - http://pastebin.com/TEkqCEGn
// $equipment_list - http://pastebin.com/408ntdFp

echo "<table border='1'>";

echo "<thead><tr>";

foreach ($category['spec_columns'] as $columnID=>$columnData) {

if (!isset($columnData['field']) && isset($columnData['children'])) {

echo "<th colspan='".count($columnData['children'])."' align='center'>".$columnData['display']."</th>";

} else {

echo "<th>".$columnData['display']."</th>";

}

}

echo "</tr></thead>";

// Second loop:

echo "<tbody>";

foreach($equipment_list as $equipment) {

echo "<tr>";

foreach ($category['spec_columns'] as $columnData) {

echo "<td><a href='index.php?action=view_equipment&id=".$equipment['_id']."'>".$equipment[$columnData['field']]."</a></td>";

}

echo "</tr>";

}

echo "</tbody>";

echo "</table>";