How should I implement this schema in MongoDB?
I'm trying to write a tracking script and I'm having trouble with figuring out how the database should work.
In MySQL I'd create a table that looks similar to
User:
username_name: string
Campaign:
title: string
description: string
link: string
UserCampaign:
user_id: integer
camp_id: integer
Click:
os: text
referer: text
camp_id: integer
user_id: integer
I need to be able to:
If I do something along the lines of
User {
Campaigns: [
{
Clicks: []
}
]
}
I run into two problems:
OK, I think you need to break this out into the basic "varieties".
You have two "entity"-style objects:
User Campaign You have one "mapping"-style object:
UserCampaign You have one "transactional"-style object:
Click Step 1: entity
Let's start with the easy ones: User & Campaign . These are truly two separate objects, neither one really depends on the other for its existence. There's also no implicit heirarchy between the two: Users do not belong to Campaigns, nor do Campaigns belong to Users.
When you have two top-level objects like this, they generally earn their own collection. So you'll want a Users collection and a Camapaigns collection.
Step 2: mapping
UserCampaign is currently used to represent an N-to-M mapping. Now, in general, when you have an N-to-1 mapping, you can put the N inside of the 1. However, with the N-to-M mapping, you generally have to "pick a side".
In theory, you could do one of the following:
Campaign ID s inside of each User Users ID s inside of each Campaign Personally, I would do #1. You probably have way more users that campaigns, and you probably want to put the array where it will be shorter.
Step 3: transactional
Clicks is really a completely different beast. In object terms you could think the following: Clicks "belong to" a User , Clicks "belong to" a Campaign . So, in theory, you could just store clicks are part of either of these objects. It's easy to think that Clicks belong under Users or Campaigns.
But if you really dig deeper, the above simplification is really flawed. In your system, Clicks are really a central object. In fact, you might even be able to say that Users & Campaigns are really just "associated with" the click.
Take a look at the questions / queries that you're asking. All of those questions actually center around clicks. Users & Campaigns are not the central object in your data, Clicks are.
Additionally, Clicks are going to be the most plentiful data in your system. You're going to have way more clicks than anything else.
This is the biggest hitch when designing a schema for data like this. Sometimes you need to push off "parent" objects when they're not the most important thing. Imagine building a simple e-commerce system. It's clear that orders would "belong to" users , but orders is so central to the system that it's going to be a "top-level" object.
Wrapping it up
You'll probably want three collections:
This should satisfy all of your query needs:
See the information from each click like IP, Referer, OS, etc
db.clicks.find()
See how many often clicks are coming from X IP, X Referer, X OS
db.clicks.group() or run a Map-Reduce.
Associate each click with a User and a Campaign
db.clicks.find({user_id : blah}) It's also possible to push click IDs into both users and campaigns (if that makes sense).
Please note that if you have lots and lots of clicks, you'll really have to analyze the queries you run most. You can't index on every field, so you'll often want to run Map-Reduces to "roll-up" the data for these queries.
The main problem i see here is that you are trying to apply the relational database concepts in to a document oriented database. The main difference between the two is that you don't worry about schema or structure in the NOSQL databases but rather about collection and documents.
It is very important/imperative to understand that there is no concepts of join in many implementations of the NOSQL as in SQL. This means if you spread your data across collections then you do a lot of work to glue it later. Also there is no other gain by spreading your data across collections as in normalizations of SQL db. You need to think what data is part of your document and which collection it applies to and never worry about implementations underneath NOSQL db. So for your problem the answer could be..and will support all you asked for...
db.trackclicks==> collection
trackclick = { OS : XP, User : John Doe, Campaign : {title: test,desc: test,link : url}, Referrer : google.com }
It is not problem for mongodb to update big amount of documents if something in some company was changed.
Have nested collection or no really depends on how much data in collection. In your case if you know that 'Clicks' collection will contain 'LARGE amount of data' you need to create a separate collection. Because for sure for the 'Clicks' you will need paging, filtering and etc. and than user will be 'light' collection.
So i suggest following:
User {
Campaigns: []
}
Clicks {
user_id,
camp_id
}