SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    canada
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help for designing a database

    Hi Everyone,

    I am working on one e-commerce project. I am designing database and programming it in ASP.NET
    I need help from you guys. Please help me for making a decision.
    My company has contract with different companies all over the world. For example Car Rental

    My Bose got contract on wholesale rate and then he added some amount and sell on the website. From US companies, he has contracts in US dollars and from Canadian companies, he has contracts in Canadian dollars (because his company is Canadian).
    From other countries he has contracts in US dollars.

    Here is the problem:- my Bose wants me to build the database that can handle both currencies (US and Canadian). So he will be able to select the currency type from internal administrator website.
    I already made a plane to do this, if I have to do.

    I want advice from all you expert programmers
    is it ok to store two different currencies in one database or not. And display on the website only one currency at a time.
    (----if the customer wants to display in US Currency on website, then convert rest of Canadian currency to US currency and display AND vice versa. ----)

    Is it ok to do this or I am creating any problems by doing this.

    Please help me.

    Thanks in advance
    Rainbow11

  2. #2
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Colorado Springs
    Posts
    308
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally, I would save only the US dollar amount, and convert to Canadian when requested on-the-fly.

  3. #3
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nekeno12
    Personally, I would save only the US dollar amount, and convert to Canadian when requested on-the-fly.

    Can .NET handle the conversion of numbers that small?

    All jokes aside I agree that you should just keep everything in USD and convert when necessary.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    folks, it's a canadian site, so keep everything in canadian dollars and convert to american when necessary

    better yet, store it as the currency it came in on, with a currency indicator
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    better yet, store it as the currency it came in on, with a currency indicator
    Rudy is right - that's exactly the way we handle metric vs. imperial floor spaces here.

    i: Store the figure the person initially submits
    ii: Store the unit that the figure is in
    (iii: Optionally store the figure converted to the other format, to save doing it on the fly, especially if you're going to be searching on it.)

    Why? Let's imagine a client uploads a property at - say - 100.00sq ft. Then imagine we would store it converted to m sq simply because we think most people use that unit. Then the client comes along to edit the details of the property. She wants to see it in the unit she uses so we convert it back to Imperial. She is presented with a property at 99.99 sq ft.

    So either she manually changes it back time after time and gets mighty annoyed, or she thinks "yeah, whatever" and saves it back to the database, and some months later ends up with a property advertised at like 77.34 sq ft. She doesn't want that.

    And I imagine you don't want it to happen to your sums of money

    Store it in the original format, and you'll only _ever_ need to convert it once, once way.

  6. #6
    SitePoint Zealot
    Join Date
    May 2004
    Location
    canada
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pointbeing
    Rudy is right - that's exactly the way we handle metric vs. imperial floor spaces here.

    i: Store the figure the person initially submits
    ii: Store the unit that the figure is in
    (iii: Optionally store the figure converted to the other format, to save doing it on the fly, especially if you're going to be searching on it.)

    Why? Let's imagine a client uploads a property at - say - 100.00sq ft. Then imagine we would store it converted to m sq simply because we think most people use that unit. Then the client comes along to edit the details of the property. She wants to see it in the unit she uses so we convert it back to Imperial. She is presented with a property at 99.99 sq ft.
    it right, I want to do the same thing

    i: Store the currency my Bose initially got contracts
    ii: Store the currency type that the currency is in
    (iii: Optionally store the currency converted to the US, to save doing it on the fly, I will be doing searching on it.)



    Letís imagine if I convert Canadian currency to US and store in database in US dollars today, but Exchange rates changes everyday.
    --I will not know what original amount store was. Rates will be less or more according to currency. This is not profitable for the business like car rental or hotel reservation system online. Because they have to keep their price low from actual hotel website in order to sell.
    --I will not able to make the correct report for the how much my Bose owe to Canadian companies in exact amount.

    I have question about third option:
    For best website performance or speed, what is good?
    Choosing the third option
    -Store the currency converted to the US, to save doing it on the fly, yes, I will be doing searching on it.
    Or
    -doing it on the fly while displaying on website. It will be more complicated doing on the fly, because I have to do so many other calculations at the same time.


    thanks
    rainbow11
    Last edited by rainbow11; Aug 10, 2004 at 16:50.

  7. #7
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good point - you're right about exchange rates (that's something we don't have to worry about when converting floor areas!)

    So - while pre-storing the converted version looks good for performance reasons, it may not be the ideal solution in your case.

    Maybe it makes sense to store the exchange rate rather than the converted amount. I don't know if it warrants a table of its own, but if .NET can cope with variables, you could drop it into a config file somewhere.

    Which begs the question of how you search on it, and order results by price...I could probably manage that in MySQL but my knowledge of M$ sql syntax isn't so good

  8. #8
    SitePoint Zealot
    Join Date
    May 2004
    Location
    canada
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks pointbeing and r937 for all you help and suggestions.

    Yes pointbeing, third option is not good for me . So I decided to store the exchange rate that I will change everyday.

    Thanks again
    I like this forums. It is very helpful.

    Iíll come back on this forums for my other questions. Talk to you later.

    Thanks
    Rainbow11
    Last edited by rainbow11; Aug 11, 2004 at 09:15.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •