How to store panel data in MySQL database (relational schema design)

usereRNw 0
Viewed 34

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
database-design
1 Answers

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)

Gilbert Le Blanc0
Related