How do I design database for an application to deal with peculier needs of multiple countries -

How do I design database for an application to deal with peculier needs of multiple countries -

when developing multi-regional application, best practises handling things differences in currencies, working days , time differences when comes database design.

do create separate database each different region/country? have database mutual tables not affected country or regional peculiarity , create separate databases each peculiar region? put them in single database , have relational parent-child references countries table, currencies table, charges table etc.

there issue of business day varies every country , every year. agent charged penalties each business day have not banked collections, since different each country, how 1 deal when designing database.

i may have muddled question bit hope essence clear enough.

unless have other requirements dictate otherwise (e.g. scalability/redundancy), best way maintain in single db instance, define table collects countries interested in (along country-specific properties).

e.g.:

country-cd |country-name | currency | language | time-zone | region-cd italy eur met emea ... ... ... ... ... ... jp state of japan jpy jp jst far-east

and utilize country code part of key each other table may have country-specific content... prices:

item-id | country-cd | valid-from | valid-to | amount x unit 950595 | | 03-may-2013 | 31-dec-2099| 23.56 950595 | jp | 01-feb-2013 | 12-aug-2013| 2643.00 950595 | jp | 13-aug-2013 | 31-dec-2099| 2810.00

so same item priced @ 23.56 euros in italy, , instead sold 2643 yen in jp, feb.1st aug.12th, , increment in cost 2810 yen.

couple of caveats

i have used iso codes country , money. should robust plenty avoid problems in future. love of god not utilize application-specific codes things states or currencies. if designing little portion of world, like, dunno... "german speaking countries" (germany, austria, part of switzerland) resist temptation utilize things ger/aus/swi or not standard. time zones horribly hairy manage. set in same table country illustrate kind of info should associated country entry, in reality countries span more 1 time zone (ru, us) , hence need more sophisticated schema. on top of point (2) above, if application lives in single-instance db have decide rule how time displayed users , stick it. consistently. see below.

the problem world-spanning applications may have server in bangalore users in rome. have 2 choices - either timestamp every operation time zone of actual server "resides" , allow users adjust mentally discrepancies, or still timestamp specific time zone (you can of course of study utilize utc instead of local time, , applies other method, too) , automagically translate user local timezone while displaying.

so may have "record created @ utc 12-may-2014 00:34" stored in db , show me "record created @ 12-may-2014 01:34 met" because using application italy.

(this prove easier users if application allows them create entries time input - illustration booking room meeting - cost more in terms of development, , may become bit complex manage when same record can updated different time zones).

these basics, really... have read i18n, of course, , decide if want error messages , ui labels multilingual. i18n cares (i.e. how manage input , output in different languages) gives guidelines more esoteric stuff culturally-appropriated icons suggest bit of research on it.

design database-design architecture