I am trying to upload a couple panel datasets to a MySQL server. But I have a couple issues when it comes to schema design.
How do I create a relational schema and combine these 2 tables together in a MySQL database?
Do I need a Star schema or a Snowflake schema?
Dataset (1): Account profile table, it has multiple clients' information in daily granularity (365 days in total). The example below shows two accounts (HP and KA) with client income, age and gender. File Date is the client account status date.
| Account ID | Income | Age | Gender | File Date |
|---|---|---|---|---|
| HP | 10,000 | 40 | Male | 2019-04-01 |
| HP | 10,000 | 40 | Male | 2019-04-02 |
| HP | 10,000 | 40 | Male | 2019-04-03 |
| HP | 12,000 | 40 | Male | 2019-04-04 |
| KA | 12,000 | 23 | Female | 2019-04-01 |
| KA | 12,000 | 23 | Female | 2019-04-02 |
| KA | 12,000 | 23 | Female | 2019-04-03 |
| KA | 12,000 | 23 | Female | 2019-04-04 |
Dataset (2): Account trading table, it has multiple clients' information in daily granularity. In the example below the first row says account HP bought google stock on 2019-06-12 for 500.00 dollars.
| Account ID | Stock ID | Trade Type | Trade Amount | File Date |
|---|---|---|---|---|
| HP | GOOG | Buy | 500.0 | 2019-06-12 |
| HP | APPL | Sell | 600.0 | 2020-03-23 |
| KA | AMZN | Sell | 1000.0 | 2020-07-23 |
| KA | APPL | Sell | 353.0 | 2020-10-13 |
| KA | MSFT | Buy | 400.0 | 2021-02-03 |
1 Answer
I didn't get an explanation, so I'm assuming that income is a synonym for balance and not additional money each day.
I'd start with an Account table
Account ------- Account ID (PK) Gender Birthdate And an Account Balance table
Account Balance --------------- Account ID Balance Date Balance Where the primary key is (Account ID, Balance Date descending).
Finally, we have a Transaction table
Transaction ----------- Transaction ID (PK) Account ID Transaction Date Stock ID Trade Type Trade Amount With a unique index on (Account ID, Transaction Date descending)
2
