Database structure design

Databases
1

Hi

If a ecommerce website is used in multiple countries (each site defined like www.example.com www.example.co.uk www.example.ie etc) how best could I set the databases up?
One point which is a must is that user accounts are unique to the country so if you want to by from .co.uk and .ie you will need to setup an account on each site.

Below is the three options I have although I’m not sure which one is best from an overall database structure and response time point of view.

[INDENT]1. Have one DB with tables which hold the exact same data across all sites then each site will have its own database which will hold unique data (customer accounts etc) relating to the site although the database structure will be exactly the same for each site.

  1. Have one big database with all tables although each site will have its own database which consists of views relating to the main DB to retrieve data relating to the specific site being used only.

  2. Have one big database across all sites with an id in tables which hold data relating to different sites so each sites data can be identified.[/INDENT]

Point 3 is the easiest if the DB structure needs to be changed although its likely some tables could end up with 10+ million rows which require near instant replies to SQL queries so I’m leaning more towards point 1.

Look forward to your comments.

2

i would go for option 1

unless of course it’s the same products in all countries

3

Hi and thanks for your reply.

In option 1 I see the country specific DB will only hold those tables which the information will differ relating to the countries site e.g. products, prices, currency etc. There would be a global database which each site will access to retrieve the data which is the same across all sites therefore not duplicating any data.

No information will be duplicated although the countries unique DB structure will be identical therefore duplicating the structure. If I make one change (e.g. add field or change a fields data type) in the UK DB is there a quick way to make the change to the other countries DB’s?
I can write ALTER table queries although is there any other possibilities I can consider such as the UK country DB structure is master so when a change is made here MYSQL automatically applies the change to the other countries DB structure?

Many thanks for your help.