Overview of the database structure for the StockTrack platform, which includes data storage for predictions, user profiles, financial metrics, stock prices, and more.
The `Predictions` table stores historical stock price predictions and actual values.
| Column | Type | Details |
|---|---|---|
| id | int | Primary Key, auto-increment |
| symbol | varchar(4) | Stock symbol (e.g., AAPL) |
| actual | decimal(7,2) | Actual stock price |
| predicted | decimal(7,2) | Predicted stock price |
| Date | datetime | Date and time of prediction |
The `User` table stores user profiles, including email, name, and group memberships.
| Column | Type | Details |
|---|---|---|
| id | int | Primary Key, auto-increment |
| varchar(225) | User's email, must be unique | |
| first_name | varchar(12) | User's first name |
| last_name | varchar(12) | User's last name |
| password | varchar(12) | User's Password |
The `Company` table holds information about different companies, including their stock symbol, sector, and industry.
| Column | Type | Details |
|---|---|---|
| id | int | Primary Key, auto-increment |
| symbol | varchar(4) | Unique company stock symbol |
| name | varchar(25) | Company name |
| sector | varchar(12) | Industry sector |
| industry | varchar(12) | Industry type |
| description | varchar(225) | Brief company description |
The `Financial Metrics` table stores key financial indicators such as P/E ratio, earnings per share, and others for each company.
| Column | Type | Details |
|---|---|---|
| company | ForeignKey (Company) | Company linked to financial data |
| pe_ratio | decimal(7,2) | Price-to-earnings ratio |
| eps | decimal(7,2) | Earnings per share |
The `Stock Price` table stores real-time stock price data.
| Column | Type | Details |
|---|---|---|
| company | ForeignKey (Company) | Company linked to stock price |
| price | decimal(7,2) | Stock price at a given time |
| date | DateTime | Date and time of stock price |
The `News Articles` table stores articles related to stocks and financial news.
| Column | Type | Details |
|---|---|---|
| headline | varchar(225) | Article headline |
| url | varchar(25) | Link to the full article |
| publish_date | DateTime | Date and time of publication |
An AWS Cloud function is integrated with the platform to automatically populate the database with the latest stock data, predictions, and other metrics.
This function pulls data from reliable financial APIs, processes it, and updates the database every hour, ensuring that users always have access to the most up-to-date information.