Is it a good practice to put the tables of different versions of a website(no data sharing among these versions) in one database?

Question

I am developing a website. There is an English version, Japanese version and Chinese version. Different version is for different language speakers. If you are a registered user of the English version, and you want to use the Japanese version, you still need to register on the Japanese version. So should I create one database and put all tables into this database or should I create 3 databases, each database for each version?

Answer 1

If these sites share no data I would say it's better to create a separate database for each. This will prevent you from accidentally damaging other version's tables if you mess up any queries.

Answer 2

make the tables reasonably separate, but don't close the door to possible future requirements. databases in mysql are a fine mechanism that fits both: it's a nice way to namespace the tables, and the separation is weak so you won't have problems with cross-database queries. use schemas in more sophisticated database systems.

Answer 3

It depends as RaYell tells on the amount of data/tables shared among these different versions. I would recommend that you look into schema support for your particular database, and partition according to schema for data separation, and by different users owning the separate schema's for security access. In Oracle database, for example, each user is assigned it's own schema, so you could have user_en, user_jp. Alternatively you could look into multilingual database design.

Answer 4

It really depends on how much data is to be shared (or combined for reporting). Even if management say "no, everything is separate" now, that'll change in 5 minutes. Always. :-)

I've worked on a number of multi-tenant systems, and would recommend a single database, designed so each site has its own ID; the negative side is the SiteID column must then be included in most of the tables, foreign keys and the associated queries. On the positive side it does allow a site's data to be extracted easily if one site is sold off, or its server is moved to a separate location for legal reasons, etc.

I'd also recommend using Unicode (or UTF-8) for all user-visible or possibly-localizable data. It'll save a lot of grief later on.

Answer 5

Definitely it is better to have separate databases, otherwise you will have to come up with different naming conventions for tables etc. If you have code that accesses these tables, then you will need to modify all that code as well instead of just reconfiguring the database bindings.

Answer 6

The answer, as usual, is "it depends." The real question, I think, is how you plan on maintaining your system.

If you are going to have a single website that allows the user to select language (or have different versions appear at different URLs), then I would use a single database, a single set of application scripts, etc. This way minor changes in schema only need to be reflected in one database. Each table with user content would have some kind of column with a SiteID column, much as devstuff recommends. A second advantage to this approach is that you can have a single user authentication system and actually let users switch from one system to another --- or eventually fuse them all together.

If you are going to have multiple applications, multiple programmers, multiple skins, etc., you may find it easier to have multiple databases. But this means that you will also have dramatically higher development costs. In some cases this is worth the trouble; in most cases it is not.