Which Database (MySql or NoSQL) for a Stock market App
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.