mysql - updating db structure - new table or adding a field -
It may look like a silly question but it is here; I'm sure this has happened around someone, you prepare a web app with a DB structure per specification (php / mysql), but then the specs change slightly and you need to make changes in the DB to reflect it Here's a small example:
order table - & gt; Order ID - & gt; User ID - & gt; Closed - & gt; Timestamp But because orders are paid in more than one different currency, which has been cited in DB, I need to add area exchange rate , Which is known not only on the entry of the record but also during the closure of the order. In this way I can either add a new field to the existing table, and leave it empty / empty when inserting it, and then update it if necessary; Or I can create a new table with the following structure: order exchange rate - & gt; Exchange ID - & gt; Order ID - & gt; Exchange rate Although I believe this letter is better because it is a less intrusive change, and the rest will not affect the functionality of the app, you need to add all necessary information. The queries may end with the crazy amount of queries. On the other hand, in the former view, you may have some other questions worse in DB, but it is definitely more practical and logical in terms of overall DB structure. Apart from this, I do not think this is a good practice to use the structure of the inserted tap and to update later, but it can only be my only opinion ... so I would like to ask you what you want Looks like a better approach.
I am thinking of one more option. Set as an Exchange Rate table:
create table exchange_date (cur_code_from varchar2 (3) not tap, cur_code_to varchar2 (3) not tap, valid date from valid_not empty, valid_date, not empty , Rate number 20,6) zero); Change the table exchange Bound exchange exchange_rate_pk primary key (cur_code_from, cur_code_to, valid_from); The table should contain data that looks like this:
cur_code_from cur_code_to valid_from valid_to rate ============= ================================ EUR EUR 2014-01-01 99 99-12-31 1 EUR USD 2014 -01 -01 99 99-12-31 1,311702 EUR SEK 2014-01-01 2014-03-30 8808322 EUR SEK 2014-04-01 9999-12-31 8,658084 EUR GBP 2014-01-01 9999- 12 -31 0,842865 EUR PLN 2014-01-01 99 99-12-31 4,211555 Note the special case when you change from the same currency with a generalization perspective , You do not need valid_to because it can be calculated from the next valid_from , but From a pragmatic perspective, it is easy to be valid, compared to using a sub-query all the time -Date Then, to convert you into the customer currency joining this table: order_value * x.rate as value_in_customer_currency from
order, o exchange_rered_t in X (X.cur_code_from = 'EUR' - your default currency here and x.cur_code_to = 'SEK' - here Customer currency and o.order_close_date between x.valid_from and x.valid_to) where o.order_id = 1234; Here I have accepted the rates as order_close_date . So if you have two orders, one with the close date of 2014-02-01, then it will take a different rate from the order with the close date of 2014-04-05.
Comments
Post a Comment