0

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
2

1 Answer 1

0

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.

4
  • Just to confirm, so the currency table is where I place the historical price of the currencies? On your example, it adds three entries (USD, CHF, EUR) on a daily basis? Then the additional table is as what you said, it updates and reflects the current price. Did I understand it right?
    – Mikuro
    Jan 9 at 16:26
  • 1
    @Mikuro Yes, you have two tables - one for historical data and one for current values, because groupwise MAX is a pretty heavy operation that can slow down reporting significantly. You can make a various reports from historical data on a non-regular basis and a fast report for "current account state".
    – Kondybas
    Jan 9 at 18:09
  • I see. Thank you so much.
    – Mikuro
    Jan 9 at 18:19
  • Btw, my Inventory table references the CurrencyId on the Currency Table. Does that mean I should now instead reference it to the new table you suggested with the current actual prices?
    – Mikuro
    Jan 9 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.