Database design suggestion

Cenk 956 Reputation points
2022-11-20T17:32:34.617+00:00

Hi there,

I have an ASP.NET web API application and am trying to upgrade it to Core. I am using EF 6, code first. I have this entity but the requirements changed. Now I have more than one price and currency for a product. How can I design the entities in order to support more than one price/currency and future additional requirements?

eg.

ClientCode: 00001, gameCode: 00001, price: 5, currency: USD
ClientCode: 00001, gameCode: 00001, price1: 5, currency1: USD, price2: 10, currency2: EUR, price3: 15, currency3: TL 

public class ProductCode  
    {  
        public int Id { get; set; }  
  
        public string clientCode { get; set; }  
  
        public string gameCode { get; set; }  
  
        public string? price { get; set; }  
  
        public string? currency { get; set; }  
  
    }
.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,415 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
ASP.NET API
ASP.NET API
ASP.NET: A set of technologies in the .NET Framework for building web applications and XML web services.API: A software intermediary that allows two applications to interact with each other.
302 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 56,931 Reputation points
    2022-11-20T18:49:38.01+00:00

    The design depends on the currency management. Are all prices in one currency and convert to another in real-time, or for each item does it have an independent value in each currency?

  2. Bjoern Peters 8,781 Reputation points
    2022-12-15T15:25:07.907+00:00

    Hi @Cenk

    from my experience with databases and webshops... I would recommend to have two tables - one for the items with your local currency and another table with conversion rates
    then there should be a function in your code, that reads both values regarding what customer had chosen (cookie, configuration or whatever) and calculation of the customers currency will happen during the page load.

    So you it is much easier to maintain the foreign exchange rates and you just have to have one price on your items.... and it doesn't matter how many different currencies you are providing.

  3. Louis Davidson 21 Reputation points MVP
    2023-02-03T02:35:47.5466667+00:00

    So what do you mean "Now I have more than one price and currency for a product."

    I guess the question is what are you trying to do, and WHEN do you want that currency translation for?

    Without requirements, my initial thought is similar to what the other designs are, but a lot depends on the temporal nature of your needs. Typically, we would store amounts in local currency. If you use your credit card in another country, when it posts it will be translated from the currency you spent in to your local currency.

    If you want to be able to translate currency for any time, then you need a table that stores exchange rates for all currencies you care about, (Probably once a day), then you can join your date attribute to the value...

    If you can sketch out the details a bit more, it would help

    0 comments