# Orderbook db structure

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!

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

• First of all thank you a lot for the HQ response Maybe a dumb question but wouldn't this cause it to be very slow when trying to read data from X-Y? – QuantKan Mar 3 at 6:47
• actually instead of 'bid' and 'offer' use 'b' and 'o' or TRUE and FALSE to save memory and possibly make the search more efficient – Attack68 Mar 3 at 6:49