0

I have a simple currency table with iso 4217 data:

currencies
----------
currency_code char(3) pk
name          nvarchar

And I want users to be able to add their own currencies (like cryptocurrencies) in a table like:

users_currencies
----------------
user_id int     pk (fk to users table)
code    char(3) pk 
composite primary key (user_id, code)

So that they can use to price a product:

users_products
--------------
user_id       int pk
product_id    int pk
currency_code char(3) (fk to users_currencies)
price         decimal

Then I would copy the currencies table into users_currencies when creating new users.

Is this a good design? What are other possibilities?

I would like to avoid this kind of copy if possible.

0

You shouldn't be copying data from the currencies table into the user_currencies table, otherwise that defeats the purpose of having a currencies table.

If users are creating new currencies (besides the ISO currencies) then you could just have a single currencies table with the currency_code (primary key), name, and possibly an isStandard BIT field that you insert into. Then your user_currencies table will just store the mapping of currencies each user has created.

Then to get all standard codes and the ones a user has created you just need to join the two tables together like so:

SELECT currencies.[name]
FROM currencies
INNER JOIN users_currencies 
    ON currencies.currency_code = user_currencies.code -- Gets only the user created currency codes
    OR currencies.isStandard = 1 -- Gets all the ISO currency codes
WHERE users_currencies.user_id = 'SomeUserId'

You can even create a View to save the above query minus the WHERE clause, and adding in the user_id field to the SELECT list, so you can later filter by a specific user.

2
  • I liked this solution, the only problem is that if two users create currencies with same code and different names. – Marcos Smith Jan 27 at 9:29
  • @MarcosSmith Not possible when the currency_code field is the primary key, this ensures unique values in that field. Though I assumed it was more of an id kind of field based on it being the PK. If you do need to also support the case you mentioned, where two currency codes can exist with different names, then you need to add a unique id field to the currencies table as the primary key as well (and use that to reference a particular currency, in both the user_currencies table and the example query I provided above, in place of the currency_code). – J.D. Jan 27 at 13:16

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.