I’m re-creating an app for Stockmarket Screening & Realtime charting Display.
The database wireframe which i propose to design is as follows:
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
) 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
Historical Data (Day wise): EXCHANGE1 CODE | OPEN PRICE | CLOSE PRICE | DAYHIGH | DAYLOW | VOLUME | Date
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.
Seeing that you already know the specific columns for your data structures and you want
I’d say a relational database is an obvious solution - in this case MySQL. No SQL databases are good for storing data which are mostly unstructured, that is have varying fields for each item, like documents that each can have a different set of properties. Relational databases will also perform much better at statistical calculations and offer better data integrity - and they are generally more structured and in this case you need structure.
However, I’d say that MySQL is the poorest choice out of available databases - but you may be fine depending on your use case and whether you might need more advanced features that are not available in MySQL. From your description I might assume that this is going to be a more serious database usage with lots of data, many write operations, frequent concurrent access, etc. Personally, I would choose at least PostgreSQL in this case.
I’m not a tech guy but purely from a logic perspective I also deduced rdbms would be better.
I managed to convince the developer in using rdbms. Mysql in this case.
But can you Pls shed some light on the specific advantages of using postgres db
Postgres is generally a more advanced database and I’d say for simple things mysql is easier and more convenient but for advanced stuff postgres is easier and more convenient. Some advantages of postgres:
generally has a better query planner, which results in better performance of complex queries requiring joins, subqueries, etc.
reported to perform better in heavy write scenarios
supports check constraints for better data integrity
supports partial indexes covering only part of a table
supports BRIN indexes for saving disk space and in the next 9.6 version there will be Bloom filter indexes
much more advanced stored functions supports with the ability to use SQL, PL/pgSQL out of the box or plug in any other programming language
overall better and more useful built-in functions including aggregate functions and ability to define custom aggregate functions
supports materialized views
has pretty advanced support for no-SQL data types in the form of JSON or hstore - MySQL’s JSON support is very recent and more basic
windows functions and many more options for grouping results (eg. ROLLUP, CUBE, GROUPING SETS), which may come in very handy in data analytic
Overall, if your requirements are simple, mysql will work well and be easier to implement. Postgres is a little bit more difficult but for serious requirements it really pays in the long run and its features can simplify queries that would need complicated workarounds in mysql.