0

I'm looking for best option that is available today with SQL Server Backend & C# has front end for storing data which has dynamic columns. Currently My Scenario is like

I have Object say "Item" which holds Pricing information for multiple currency like

ID     Name    AUD  USD CAD INR 
1      Item1    1    1    1   1

As of today we have fixed number of currency (# of Currency to display is based on XML Configured Currency File), we currently implemented table with fixed number of currency. Let assume tomorrow if there is new currency added say EURO without doing much changes to front End, ( I can assume datatable in this scenario) and no major changes to backend tables, do we have any new features either in SQL Server or C# which supports dynamic columns ?

One Approach which I'm thinking of (Old approach) Create ITEM Class with ItemID , ItemName and Dictionary/Expando Object/ Property BAG with Currency prices like

Item1 RAM 12

Is it better to seralize those PropertyBag/Expando Object in XML or JSON, which is faster to Save and Retrieve.

I would like to view all Currencies for each item in Relational/Table Format while I Query.

How can i have datatable with PropertyBag.

StepUp
  • 36,391
  • 15
  • 88
  • 148
user145610
  • 2,949
  • 4
  • 43
  • 75
  • 1
    Sounds like a good candidate for normalisation – Diado Aug 31 '18 at 09:00
  • 2
    Instead of having a seperate column for each currency, have a column (let's call it CurrencyID) which stores the ID for each currency (with a lookup table containing the currency information) and use that. No need to add more columns for new currencies! – MJH Aug 31 '18 at 09:05

1 Answers1

1

Stop storing the value in different currency columns.

Have the base local currency for where you are operating and use a currency api to offer different currencies and calculate prices.

With an API, you can offer all currencies that are provided with a live exchange rate.

Exchange rates change all of the time, so to avoid having to update prices in you tables, get the live exchange rate and use that.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • Thanks Tanner. Every day we have 30K item which needs to be updated for 10 currience. totall 30KX10 needs to be stored and retrieve on daily basis. I'm thinking of having all the currience searilized and store in a varchar column...rather than storing 10 curriencencies in 10 columns..or storing 10 currience has 10 rows in a table... – user145610 Sep 03 '18 at 06:35
  • If you don't want to go down the API route, then I'd suggest doing what @MJH states in the comment on your question. – Tanner Sep 03 '18 at 08:01