Please excuse me for the rather long post, but I think it’s required to make the question clear.
Looking into selling products online at different currencies which led me to analyse how the big guys Amazon do it and how Microsoft do it.
From what I can see Amazon, have a separate domain, web site, and database specific to a region / country; so it’s pretty much working in silo’s. Okay, but not great.
But Microsoft seems to do it so much better - same domain, same site, same database, but differentiating on the culture e.g. en-US, en-GB, en-AU etc. So no silos and am guessing they are looking at other factors such as browser locale and/or IP address i.e.
geo-location.
However, Microsoft is not just using exchange rates to convert from USD to a foreign currency. Instead they have very different price points for different regions / countries.
For instance, the Microsoft Web Site shows the following prices for the new Surface device.
USA USD $499
UK GBP 399
Australia AUD $559
Germany EUR 479
Adjusted prices - omitting the minor units
USA USD $499 baseline
UK GBP 313 difference GBP 86 +27.5%
Australia AUD $480 difference AUD 79 +16.5%
Germany EUR 390 difference EUR 88 +22.5%
Any ideas or clever guesses on how Microsoft has
architected the database tables for the product store in terms of the
international price attribute?
In other words, on the database Product table, would they have individual columns for each currency?
For example: Product table columns: ColProduct ColUSD ColGBP ColEUR ColAUD and so on
Or do you think would they have a one to many relationship between a Product table and a Prices tables?
For example: Table Product (1) ---> Table Prices (N)
Or would it be a many to many between the Product table and the Currencies table with Prices as the cross reference table?
For example: Table Product (1) ---> Table Prices (N) <--- Table Currency (1)
From what I can see Amazon, have a separate domain, web site, and database specific to a region / country; so it’s pretty much working in silo’s. Okay, but not great.
How you determined that Amazon having separate database for region/country?
In other words, on the database Product table, would they have individual columns for each currency?
For example: Product table columns: ColProduct ColUSD ColGBP ColEUR ColAUD and so on
Or do you think would they have a one to many relationship between a Product table and a Prices tables?
For example: Table Product (1) ---> Table Prices (N)
Or would it be a many to many between the Product table and the Currencies table with Prices as the cross reference table?
For example: Table Product (1) ---> Table Prices (N) <--- Table Currency (1)
I don’t think they are using any of above approach.
Generally one should save price in only one currency let say INR and consider it as a base price and base currency. Now whenever price is displayed in other currency let say USD then it should be converted in USD based on current conversion factor + any
additional hedging charge. Sometime it may happen that system store conversion factor against transaction so it can be used for future analysis or sometime also used for refund calculation.
Keep it Simp...
Member
549 Points
264 Posts
International Price Attribute Schema
Nov 19, 2012 09:21 AM|LINK
Please excuse me for the rather long post, but I think it’s required to make the question clear.
Looking into selling products online at different currencies which led me to analyse how the big guys Amazon do it and how Microsoft do it.
From what I can see Amazon, have a separate domain, web site, and database specific to a region / country; so it’s pretty much working in silo’s. Okay, but not great.
But Microsoft seems to do it so much better - same domain, same site, same database, but differentiating on the culture e.g. en-US, en-GB, en-AU etc. So no silos and am guessing they are looking at other factors such as browser locale and/or IP address i.e. geo-location.
However, Microsoft is not just using exchange rates to convert from USD to a foreign currency. Instead they have very different price points for different regions / countries.
For instance, the Microsoft Web Site shows the following prices for the new Surface device.
USA USD $499
UK GBP 399
Australia AUD $559
Germany EUR 479
Exchange Rates
1 USD : 0.628 GBP
1 USD : 0.963 AUD
1 USD : 0.783 EUR
Adjusted prices - omitting the minor units
USA USD $499 baseline
UK GBP 313 difference GBP 86 +27.5%
Australia AUD $480 difference AUD 79 +16.5%
Germany EUR 390 difference EUR 88 +22.5%
Any ideas or clever guesses on how Microsoft has architected the database tables for the product store in terms of the international price attribute?
In other words, on the database Product table, would they have individual columns for each currency?
For example: Product table columns: ColProduct ColUSD ColGBP ColEUR ColAUD and so on
Or do you think would they have a one to many relationship between a Product table and a Prices tables?
For example: Table Product (1) ---> Table Prices (N)
Or would it be a many to many between the Product table and the Currencies table with Prices as the cross reference table?
For example: Table Product (1) ---> Table Prices (N) <--- Table Currency (1)
Or would it be something else entirely?
Thanks
Nandip Makwa...
Participant
1283 Points
296 Posts
Re: International Price Attribute Schema
Nov 20, 2012 03:32 PM|LINK
How you determined that Amazon having separate database for region/country?
Software Engineer by Profession, Learner by Passion!
Nandip Makwa...
Participant
1283 Points
296 Posts
Re: International Price Attribute Schema
Nov 20, 2012 03:50 PM|LINK
I don’t think they are using any of above approach.
Generally one should save price in only one currency let say INR and consider it as a base price and base currency. Now whenever price is displayed in other currency let say USD then it should be converted in USD based on current conversion factor + any additional hedging charge. Sometime it may happen that system store conversion factor against transaction so it can be used for future analysis or sometime also used for refund calculation.
Hope this would be helpful.
Software Engineer by Profession, Learner by Passion!
Keep it Simp...
Member
549 Points
264 Posts
Re: International Price Attribute Schema
Nov 20, 2012 10:56 PM|LINK
With your account, try to login to another region.
When the system comes back and says you do not have an account, you can assume it's a different authentication store i.e. different database.
Keep it Simp...
Member
549 Points
264 Posts
Re: International Price Attribute Schema
Nov 20, 2012 10:59 PM|LINK
I thought of the same thing this morning and am glad you suggested and confirmed this approach.
Thank you.