Orderbook db structure

Question

I am currently saving a sub 1 sec snapshot of an orderbook to my SQL db. However I have quite the trouble on figuring out the architecture of this DB

What I'm currently doing is saving a table with the data and naming the table the current epoch. As you can see this is a horrible way of doing it.

What would you guys recommend on doing to make this more efficient and less messy.

The data i'm currently storing is only: time,size,price Thanks in advance you guys are awesome!

Answer 1

Have you considered storing it by adding the additional fields ('level' or 'depth') and ('type'), then you can have a table which looks like this;

time    size    price    depth    type
xxx     100     100.03   1        bid
xxx     2000    100.025  2        bid
xxx     0       100.02   3        bid
xxx     33      100.035  1        offer
xxx     45      100.04   2        offer
xxx     550     100.045  3        offer

Additionally you can even compare the next timesnap with the previous and only create a new entry if data changes. The smaller your time difference the greater the data storage efficiency will be. For example suppose the next time step there were only two changes; a new level 3 bid and the offer had been reduced at level 1 due to trading or ortherwise, then you only create 2 rows;

xxx+1    250    100.02   3      bid
xxx+1    23     100.035  1      offer

You can always index the table for a given timestamp with a simple SQL;

SELECT TOP 1 * 
    FROM table 
    WHERE type='bid' AND level=1 AND time <= xxx+1
    ORDER BY time DESC