[thelist] ASP/SQL Solution for Double Currency Web Site

[rudy]

>varCDNPrice = rst("Price")
>varExRate = 0.7
>varUSPrice = (varCDNPrice * varExRate)
>
>Is the above reasonable?

hi michele

yes, as long as you update the page when the rate changes

> Or am I better off having BOTH prices in my database?

definitely NOT, if you're thinking of storing the canadian and american
prices on the same item, that would be a redundancy

> I'm wondering if I should actually be storing my "varExRate" in
> my database? The reason being, the Exchange Rate is used
> on several different ASP pages.

yes, and for all the reasons that you go on to cite

<tip type="data design">
  there is nothing wrong with having an OOAK (one of a kind) record or
table in your database
  example:
         create table USexchangeRate
               (theRate  numeric(9,7))
  this table would typically have one row
  add a timestamp and you have multiple rows giving a historical record of
rate fluctuation
  add a country code (and remove "US" from the name of the table) and you
again have multiple rows for an international application
</tip>

> So, what can I do?  What do others do?  If I put it in the DB, as
> its own table, one record

say, have you been reading my answers?   ;o)

> this will require me to make a second call to the DB to get
> this value out.. I think.  I don't believe I can use an "AND" within
> a SELECT statement in SQL, but I could be mistaken on this.

not a second call, no

yes, sql does have an AND, but you're looking for a table join

here's your current select statement --

   SELECT Stuff, Price
       FROM ceramics

here it is with a table join to the OOAK record shown above

   SELECT Stuff, Price,
        Price * theRate as USPrice
          FROM ceramics, USexchangeRate

hey, where's the WHERE clause?   i am sure you must be wondering

usually, tables are joined with a WHERE clause (or ON condition) that
specifies which rows of table 1 get joined up to which rows of table 2

when the join condition is missing, *every* row of table 1 gets joined with
*every* row of table 2

in your case there is only one row in the USexchangeRate table!!  think
about which rows from the ceramics table you will get back   ;o)

neat, eh?


then, all you need is an admin function that lets you change the exchange
rate, something like

   update USexchangeRate
     set theRate = #form.newUSrate#

note the absence of a WHERE clause indicating which rows to update, so all
of them will be updated -- but there's only one row!!  (you'd have to use a
different approach if you were timestamping your rates)

> Or, should I just drop this rate (essentially, the three lines of
> code presented above), into an include file, then call the include
> file as needed in the various ASP processing pages?

yeah, that'll work, except you're doing the calculation in a web server
script, which is probably slower than doing it in the database, which is a
lot simpler -- to me, anyway, but then i'm a raging lunatic database guy

but yeah, the include method would work fine too


rudy.ca