What is good approach in storing price changes of a product / currency?

Question

I am building a system where users can own a currency pretty much like crypto. The idea is that the value of that currency changes every 24 hours based on certain factors.

Here's an example. There's a currency called "Coin" (CurrencyName) with an initial value of $1. The value changes every 24 hours, say the next day it becomes "$1.14". What's the best approach to record daily price? Should I create a "Value" column in the Currency Table and just update it every 24 hours while also updating another table say "Value History Table" for historical record?

User Table

• UserId INT PK
• Email VARCHAR(255)
• Password VARCHAR(255)

Currency Table

• CurrencyId INT PK
• CurrecyName VARCHAR(255)
• TotalSupply INT

Inventory Table

• CurrencyId INT FK
• UserId INT FK
• AmountOwned INT

Answer 1

Add a datetime column to the Currency table:

+-------+------------+---------+
| CCode | PDate      | Price   |
+-------+------------+---------+
|   USD | 2022-01-09 | 1.00000 |
|   CHF | 2022-01-09 | 1.10712 |
|   EUR | 2022-01-09 | 1.22714 |

Also add another table with current actual prices:

+-------+---------+
| CCode | Price   |
+-------+---------+
|   USD | 1.00000 |
|   CHF | 1.10712 |
|   EUR | 1.22714 |

This table should have UNIQUE index for CCode column and should be inserted with
INSERT ... ON DUPLICATE KEY UPDATE Price = NewPrice;

N.B. Do not use `VARCHAR(255) if you don't really need 255 characters.