Help with a multi-language medical database

Question

My project is to design a database for medicines in many countries.

• Medicines have various criteria like composition, price, manufacturer.

• Database will be multi-language; related to each country.

• Database will increase in size over time.

• Database will have more than just other data on medicines so it will become more complex over time.

Should I make a separate database for each country?

I use SQL Server 2008.

Answer 1

No, you should not split this out into different databases. This will cause more issues and complexity further down the line, for reporting cross country, possibly requiring cross database ownership chaining, etc.

You can achieve your requirements by a process of normalization. Many people say they have normalised a database by looking at the required attributes and splitting them into separate entities by "feel". However, proper normalization is a much more formal, structured approach and is applicable for good database design. There is a very good Wikipedia article at http://en.m.wikipedia.org/wiki/Database_normalization and Itzik Ben-Gans book T-SQL Querying also goes into lots of detail with good examples.

Also recommend that you validate your normalization with a full ERD (Entity Relationship Diagram) Your normalization and ERD are also very useful design documents for your projects documentation library.

Answer 2

In general, you should not split one logical set of data into separate databases. It brings little benefit and makes administration and development more difficult. There are exceptions, it sounds like you don't have one.

Attribute splitting

Putting one logical set of data in more than one place in the schema is called attribute splitting. Joe Celko covers the topic in his book SQL for Smarties:

Attribute splitting takes many forms. It occurs when you have a single attribute, but put its values in more than one place in the schema. The most common form of attribute spltting is to create separate tables for each value. Another form of attribue spltting is to create seprate rows in the same table for part of each value.

He continues with a classic example of attribute splitting at the table level:

If I were to create a database with a table for male employees and separate table for female employees, you would immediately see that they should be one table with a column for a sex code. I would have split a table on sex. This is very obvious, but it can also be subtler.

If you were to create a separate database for each country's medicine data, you would be splitting on the country attribute at the database level.

Practical reasons to split a database

In his Splitting Data article, Joe Celko gives examples of column splitting, table splitting, and schema (or database) splitting. He concedes that database splitting is sometimes a practical necessity:

The extreme case is putting data into multiple databases. This is done out of necessity most of the time. It is not always physically or practically possible to put all of the enterprise data into one centralized database. This is fine as long as it is logical and well controlled. We want a single data model of an enterprise. This is where a federated database is similar to a partitioned database.

In a federated database, the system maintains the relationships among the databases and (we hope) keeps the data consistent (i.e., every DB is on UTC, a set of measures, etc.) so that the data belongs to one central data model and not a bunch of local data models that do not match.

One practical reason to split a database would be if you have more data than your system can manage in a single database. For example, if you have more than 10 GB of data, and you are subject to the limitations of SQL Server Express Edition, you will have to store the data in multiple databases.

Problems caused by splitting a database

Until you can identify practical problems of having too much in one database, the added complexity is definitely not worth it. A federated database system, being a distributed system, is much harder to manage than a single database.

For example, if you have multiple databases, you cannot guarantee the atomicity of backups, because system backups work at the database level. If you are backing up multiple databases, how do you ensure that restoring from backups would return all databases to a consistent point in time?

Similarly, SQL Server can not guarantee referential integrity across databases. You can't declare a foreign key column in one database that references a column in another database. If the integrity of your data is important to you, you should design your database so that the system can ensure its integrity automatically.

As Pete Carter explained in his answer that it would be more difficult to query and report across all countries.

'Splitting for security'

You haven't specified access requirements, but let me suggest one. A hypothetical business requirement that might tempt a designer to split a database would be a security rule that operators in one country should not be able to view the medicine data of another country.

An common argument for implementing this requirement by splitting the database is that it is easy to restrict access at the database level.

SQL Server provides ways to protect your data without creating multiple databases. You could create per-country views and allow access to the data only through views.