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.

  • 1
    From your comments, it seems like the core difficulty you face is how to model multilingual data. If you post a link to a new question with example data and your attempt to model it, we might be able to help you solve this problem without resorting to attribute splitting. – Iain Samuel McLean Elder Oct 5 '12 at 10:05
  • The OP posted a follow-up question about database design. – Iain Samuel McLean Elder Oct 6 '12 at 13:09

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.

| improve this answer | |
  • database has many sections not only about medicines ... i started with medicines part and after normalizing it .. i had many tables related to that part only .. and all that for the english language only ... that's why i feel having all languages i want in the same db will make it more complicated – Hatem Ghazy Oct 5 '12 at 7:41
  • Well, I can only advise you from my experience and knowledge and a project that I inherited was split into separate DBs by region and data area, and that worked ok as a tactical solution, but not strategically. We are currently re-writing the solution to conform it into a single hub. If the data belongs together logically then it belongs in the same DB. – Pete Carter Oct 5 '12 at 7:54
  • i looked through all previous questions along stackoverflow, all solutions were around have the same products but with different languages ... but no one asked about if we have totally different products not related to eachothers – Hatem Ghazy Oct 5 '12 at 9:32
  • If you have different logical sets of data, you might consider creating different schemas for each of your logical sets. But creating different databases for the logical sets, except in extreme cases, causes unjustified administrative difficulties. See my answer for more detail. – Iain Samuel McLean Elder Oct 5 '12 at 9:59

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.

| improve this answer | |
  • 1
    Another practical reason for splitting a database: resources. The 10GB Express limit is a very minor variation on this. How about large databases that won't ever fit into cache on a single machine? I implemented a tenant model where each client got their own database. We had over 500 databases per instance, and the nice thing about this: if a client outgrew their instance, a database is much easier to move to a new/different instance than a schema or a set of tables. – Aaron Bertrand Oct 5 '12 at 19:41
  • thanx for ur answers ... i will answer my questions again but with image for what i designed in my db ... and with more details about my project – Hatem Ghazy Oct 6 '12 at 3:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.