My project is to design a medical database for many countries.

This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language.

I started my design with medicine part but I'm having many issues continuing:

  • How can I put all these complex parts in one database with different languages? I can't imagine any suitable design for that complex idea.
  • How can I deal with the database growth from data coming fast for each part from each country?
  • I read about primary files and secondary files and how they increase performance of a database. Should I made them for my database? Or should I use the default files?
  • Should I separate every country with its own parts in a separated database?

I uploaded 2 pics for the initial design for medicines part in English language only.

NOTE: the 2 pics are only one but it's too large so I divided it into 2 halves

I use SQL Server 2008.

part1 part2

  • 1
    @Mat It might help to know the context of the question. It's a follow up from a question about splitting datbases. The OP already has answers to the database-splitting question there. – Iain Samuel McLean Elder Oct 6 '12 at 14:59
  • about DB growth ... u can imagine how medicines number increase everyday ..and how medical field grow every min ...so i cant decided exactly how will be the growth ..but i prepare a design support me for long time with no future troubles. – Hatem Ghazy Oct 6 '12 at 15:25
  • about DB design ... i sugggested that every country should have a separated DB with all its own parts ..but many experiences people adviced me to avoid that and it's not good idea ...so i uploaded my initial design for medicines part to make them see how it will be complicated to have all parts in smae place ... and how i have all these things with different languages – Hatem Ghazy Oct 6 '12 at 15:27
  • about DB physical layout:i cant separate that part from my design ..it should be considered before start working in DB coz it will affect the design, growth, and performance – Hatem Ghazy Oct 6 '12 at 15:30
  • 1
    @HatemGhazy We can help you find a model for your multilingual data if you put more information in the question about which data is varies by language. I could assume that the columns ending in _Name could contain English names, Chinese names, Spanish names, and so on, but I don't want to assume wrongly. – Iain Samuel McLean Elder Oct 6 '12 at 15:39
  • How can i put all these complexed parts in one DB with different languages??

Define the requirements

I can give you an example of how to model multilingual name data by assuming these business requirements:

  • The database should store the names of the ingredients of drugs in English, French and Spanish, and more languages yet to be defined.
  • Each ingredient is known by one name in each langauge.

Identify the languages

You need a way to identify langauges in your database so that you can associate drug names with a particular langauge.

A simple standard for identifying languages is ISO 619-1, which defines a two-letter code for the world's most common languages. For example, the code for English is en, French is fr, and Spanish is es.

The following SQL creates a table with a language code column, then populates the table with the example languages:

CREATE TABLE Languages (
  LanguageCode CHAR(2) NOT NULL,
  CONSTRAINT PK_Languages PRIMARY KEY (LanguageCode),
  CONSTRAINT CK_LowerAlpha CHECK (LanguageCode NOT LIKE '%[^a-z]%' COLLATE Latin1_General_BIN2)

INSERT INTO Languages (

There is nothing in your entity model that corresponds to this table, which could explain why you found it difficult to model multiligual data.

Identify the chemicals

Next you need a way to identify the chemicals that compose each drug.

A common way to identify chemicals is CAS registry numbering, which defines a numerical code for every chemical known to Science. For example, the code for water is 7732-18-5, formaldehyde is 50-00-0, and mustard oil is 8007-40-7.

The following SQL creates a table with a chemical code column, then populates the table with the example chemicals:

CREATE TABLE Chemicals (
  ChemicalCode VARCHAR(12) NOT NULL,
  CONSTRAINT PK_Chemicals PRIMARY KEY (ChemicalCode),
  CONSTRAINT CK_Dashes CHECK (ChemicalCode LIKE '%_-__-_'),
  CONSTRAINT CK_Digits CHECK (REPLACE(ChemicalCode, '-', '') NOT LIKE '%[^0-9]%')

INSERT INTO Chemicals (

The Chemicals table roughly corresponds to the Drugs_Ingredients entity in your diagram, but lacks a column for the name.

For simplicity, the table does not enforce the checksum constraint. You should enforce the checksum constraint before using CAS registry numbers in production.

Identify the chemical name

If you were to store the name in a column in the Chemicals table, it would be difficult to support more than one language. You could have one column per langauge, but you would have to modify the schema every time you wanted to support a new langauge.

To free yourself from these limitations, you should model the chemical name as an identifiable entity in itself. You can identify the chemical name using a compound identity of the chemical code and the language code.

The following SQL creates a table with columns for the chemical code, the language code, and the checmical name, then populates the table with one name in each language for each chemical:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode),
  CONSTRAINT FK_ChemicalNames_ChemicalCode FOREIGN KEY (ChemicalCode) REFERENCES Chemicals(ChemicalCode),
  CONSTRAINT FK_ChemicalNames_LanguageCode FOREIGN KEY (LanguageCode) REFERENCES Languages(LanguageCode)

INSERT INTO ChemicalNames (
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

The ChemicalName column is of type NVARCHAR so that it can store any Unicode string. You should always use Unicode in international applications to store text so that you can handle the writing systems of different langauges in a uniform way.

Query the chemical names

I can think of two questions that you might reasonably ask of your database. With the ChemicalNames table, you can answer both of them with simple queries.

What are the names of all the chemicals in Spanish?


SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';


50-00-0 Formaldehído
7732-18-5   Agua
8007-40-7   Aceite de mostaza

What is water called in each langauge?


SELECT LanguageCode, ChemicalName
FROM ChemicalNames
WHERE ChemicalCode = '7732-18-5';


en  Water
es  Agua
fr  Eau
| improve this answer | |
  • mmmmmmm ... now i can got u ... i made table for languages i want ..and link it to all my other tables ... so another column will be added to all tables for language code ,and all data in all tables will be classified to language.... U R BRILLIANT ... great thanx for ur support @isme and ...and thanx for ur all time ... are there any other advices u want to tell me?? – Hatem Ghazy Oct 7 '12 at 11:43
  • great thanx too for @PeterSmith for his support wish to find ur advices always around me ... thanx for ur time – Hatem Ghazy Oct 7 '12 at 11:44
  • @HatemGhazy If you have found my answer most useful, please officially accept the answer by clicking under the voting buttons. Doing this shows everyone that you consider the problem solved. – Iain Samuel McLean Elder Oct 7 '12 at 12:14
  • i tried but website tell me i must have 15 reputation to be able to do that ..when i increase ..i will do it immediately ..thanx again – Hatem Ghazy Oct 7 '12 at 15:38
  • @ isme sorry for asking again .... but according to ur design u introduced .. all medicines for all countries with all languages will be in one table .. and the same for every thing ... each table will contain the whole data for all countries with all languages...that mean DB of the software in any country will contain un-neccessary data about other countries .. and un-neccessary overload on machine – Hatem Ghazy Oct 8 '12 at 14:03

Multiple DBs - This is an exact repeat of the thread you started yesterday. The answer is still a single DB.

Multiple Files - Yea, multiple files help for performance, but they need to be on separate LUNs or you will get no benefit. Also separate out Lig file into its own spindle and TempDB into its own spindle. You should also consider multiple file groups so that you can control or at least aid parellel reads/writes.

Auto grow - you should perform capacity planning before deciding on the initial file size and appropriately size the files to avoid them growing if possible. Auto growth should still be set as a safe guard

For multiple languages - I suggest you use UNICODE data types for text and store the different languages in the same tables/columns, thus reducing the complexity of your model.

| improve this answer | |
  • the results till now: i will use only 1 DB for my project , i wont make any multiple files for my DB and i use the default files – Hatem Ghazy Oct 6 '12 at 17:54
  • about capacity planning: i dont know how to do it? every medicine will have its own image with a description for it , i may have thouthands of medicines in that DB ..may be millions ... the same will be for the other parts who is related to the rest of medical carrer – Hatem Ghazy Oct 6 '12 at 17:56
  • about making all languages in the same tables by adding columns for each language ... will that allow me to add new languages?? and will be easy to modify or update tables?? what about performance? – Hatem Ghazy Oct 6 '12 at 17:59
  • @HatemGhazy I think Pete means that you should store all the name data in one column. See my answer for a simple example. If you were to store the each language in different column, you would be splitting on the language attribute at the column level. It would be difficult to query and difficult to change the supported languages. – Iain Samuel McLean Elder Oct 6 '12 at 20:39
  • 1
    @Isme - SQL server is able to perform operations in parellel, but for most standard operations such as reads and writes, it can only open 1 IO thread per IO path. This path could be a single spindle, a RAID array or a LUN on the SAN. If you created 2 logical discs on the same spindle you would be unlikely to get the benefit because the head of the spindle can only be in 1 place at a time, regardless of IO threads. In fact, the best practise around splitting files even log onto separate drives in pointless if they all point at same physical spindles on the SAN – Pete Carter Oct 8 '12 at 17:10

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.