11
$\begingroup$

I'm re-creating an app for Stockmarket Screening & Realtime charting Display.

The database wireframe which i propose to design is as follows:

1. Company master - Where all the information of the company is given: Vendor Code|Company Full Name|Company Short name|Industry Code|Industry Full Name|Promoter Group Code|Promoter Group|EXCHANGE1 CODE|STOCK CATEGORY|EXCHANGE2 CODE|TYPE|ISIN CODE|STOCK TYPE

2. ) Intraday Data - Where every minutes the price of a stock is Stored: (This would be overwritten the next trading day) EXCHANGE1 CODE | OPEN PRICE | LAST PRICE | DAYHIGH | DAYLOW | Offer Price | Offer Qty | VOLUME |VALUE |Date & Time Stamp

3. Historical Data (Day wise): EXCHANGE1 CODE | OPEN PRICE | CLOSE PRICE | DAYHIGH | DAYLOW | VOLUME | Date

4. Fundamental Data (Not Yet given full thought): This would store all fundamental data like last 4 qtrly reports, Competitors, Balance sheets, Financial Ratios, P&L Statement, Promoter Details etc..

Typical Queries would be:

Stock Quote Page: Time Series Price Charts with user selection, Intraday, 1 week, 1 month, 6months, 1 year, 5 years

Fundamental Data presented in chart form (i.e growth in profits, growth in sales) plus Other fundamental Data & News

Stock Screening : (Example Queries)

  • Show me the stock of companies who have grown their sales by 20% per year over the last 3 years

  • Show me the companies whos PE is less than 10

  • Show me the company whose qtrly profit has grown by 15% per year over past 5 years

  • Show me the companies in Automobiles sector whose last 100 day avg price is less than current price
  • Show 50day, 100day, 200day simple moving averages etc etc..

Right now i'm at a stage wherein i've to decide which database to use MySQL or MongoDB (NoSQL Document) or Cassandra (NoSQL Column). So in the above case which database should i use? and why? (Advantages/Disadvantages) I want fast execution, data integrity, high concurrency, data aggregation & calculations (Analysis).

Plus we have to account that the data tables are being updates every minute and also serving visitor requests from the same DB simultaneously. So consistent & error free read/write is also of importance.

Any comments/critiques on my DB wireframe also welcome.

Regards Sunny

Improve this question
$\endgroup$
4
  • $\begingroup$ Have a look on couchdb $\endgroup$
    – Aurigae
    Commented Apr 10, 2016 at 9:54
  • 1
    $\begingroup$ Your question is like: "I need to work with screws and nails, should I use my hammer or my screw driver?" Different needs, different tools... $\endgroup$
    – Dr_Sam
    Commented Apr 12, 2016 at 6:35
  • $\begingroup$ Hey Sunny, don't take this the wrong way, but do you have any DBA experience? From the comments posed in your question, I can guess with near certainty that you don't. As such, I advise you hire an expert, or at least someone with some practical experience to do the initial design work. Getting the initial design correct is worth its weight in gold. $\endgroup$
    – Eric
    Commented Apr 13, 2016 at 23:45
  • $\begingroup$ Hi Eric, Ye you are absolutely right, i don't have an inkling about DBA. And actually we've hired a developer. My reasoning of posting the above question was to arrive at an researched decision. Our developer had initially suggested to go for NoSQL because of the fast query/response reasoning. But because of the comments on this thread we came to an mutually agreed decision of going with MySQL, hope it was a logical decision. $\endgroup$
    – Sunny
    Commented Apr 18, 2016 at 5:27

3 Answers 3

Reset to default
7
$\begingroup$

An SQL database is generally best for structured data, ad-hoc queries and for queries involving joining several entities together to find the results. It will also help you maintain data consistency and integrity by forcing this more structured design. Recent in-memory features of modern database engines offer most of the remaining performance advantages of NoSQL.

If the fundamentals part of your database consists of unstructured data (such as file attachments), then NoSQL is a good choice here. But an SQL table with file name references works almost just as well.

My recommendation for your project is PostgreSQL, but if it has to be one of the mentioned then MySQL.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Thanks Finn for your recommendation, we've gone with MySQL as the developer had past working experience with MySQL. $\endgroup$
    – Sunny
    Commented Apr 18, 2016 at 5:46
4
$\begingroup$

You're going to want different databases for different data. For instance, the company master, historical data, and fundamental data can probably all live in a standard SQL database (MySQL or Postgres are both reasonable choices).

If the intraday data is relatively low-frequency (e.g., 1-minute bars or lower), that can probably be put into the SQL DB as well. If it's very large (e.g., tick-by-tick data from multiple exchanges), then SQL might not be a great choice. In that case, you will want to go with something like HDF5 files, or even a custom file format depending on what kind of queries you want to do.

When data gets very large, it's important to think about exactly what kind of queries you're going to be running on it, and then optimize the data structure (backend, file format, column layout, etc) to suit your queries.

Improve this answer
$\endgroup$
1
  • $\begingroup$ Hi Thomas thanks for the input. Intraday (1minute frequency) we are storing only for past 5 days, rest of the data is EOD for 15 years (OHLCV+AdjVal). For standard repetitive queries we are thing of using sever RAM to serve the data (Prepopulating the memory at fixed intervals), i see logic in this, but dont know the technical pitfalls as yet. $\endgroup$
    – Sunny
    Commented Apr 18, 2016 at 5:48
0
$\begingroup$

Some Quantitative department use Arctic which is a timeseries/dataframe database built on top of MongoDB.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.