MongoDB: Schema design for stock tick database

Question

I need to store daily stock closing prices as well as tick data in MongoDB. How would you design such a schema? For daily prices I would be tempted to have one document for each stock symbol, e.g.

{
    symbol: "AAPL",
    quotes: {
        {
           date: '2014-01-01',
           values: { open: 1, high: 1, low: 1, close: 1, volume: 100 }
        },
        {
           date: '2014-01-02',
           values: { open: 1, high: 1, low: 1, close: 1, volume: 100 }
        }, ...
    }
}

For tick data I could do something like the above with one subdocument per hour with an array of ticks.

However, considering the maximum document size is only 16MB I believe the limited would be reached very fast, especially for tick data.

I am aware of this approach http://blog.mongodb.org/post/65517193370/schema-design-for-time-series-data-in-mongodb. Would that be a good approach? I.e. one document per symbol per day?

So, how would you design the schema for daily prices and tick data, respectively?

Answer 1

I think you are on the right track.

• Having one document for each stock symbol will give you a good overview of all the symbols in the collection. And each document will have a fairly maintainable size.
• In my opinion, if you are even close to 16MB on a single document, the schema-design is far from good enough. It's not easily readable or maintainable. You also have to fetch a whole lot of data each time you want anything from the document.
• You mention "one docuement per symbol per day". To me that sounds like a sensible way to structure the data. Although i'm not familiar with the details in tick data from stocks, I supposed this will give you a good foundation for the schema design. You split it by each day, and can easily get all ticks for a given day/hour.
• Remember, there is no absolute solution to schema-design, as long as you think through it thoroughly. (there is definitely a right/wrong way though) ;)