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.