I'm working on a DB design for medicines in many countries with different languages. I created a table for the different languages I want and link it to another table of chemicals. But I find that only one table will contain all medicines for all countries with all languages and the same applies for other tables linked to the language table. Ths means that the software in any country will contain unneccessary data about other countries and unneccessary overhead on the server.

How can I solve this design problem?

See my question to understand what I mean.

  • If you want to distribute/use the same software in many countries, you could use the same design but load only necessary data in each version. English data only for USA, Greek data for Greece, English and French data for Canada, etc. – ypercubeᵀᴹ Oct 8 '12 at 18:28
  • i know .. but how to separate the data with the different languages in my design?? – Hatem Ghazy Oct 8 '12 at 18:39
  • In what way do you need to separate it? You say you have a language table joined to the medicine table, so when you query the data, just have the desired language in the WHERE clause. Essentialy, your medicine table is like a fact table and your language table a dimension. You just filter by the dimension key. Sorry if I am misunderstanding you point? – Pete Carter Oct 8 '12 at 20:20

Previously I assumed that all users would connect to a central database. Here it sounds like you want to have a copy of the database local to each team.

The decision to have one or many instances of the database might affect which data is stored in each instance, but it should not affect the logical model that relates languages, chemicals, and the names of chemicals.

SIngle-instance solution

It should be clear that a central database would have to store all languages, all chemicals, and all names so that each team could use it. For example, the database would store a code for every chemical that is studied by each team, a code for every language spoken by each team, the name of every chemical in every language.

I assume that the database has a table like this to store the names of chemicals in each language:

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)

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');

I assume that the user interface of the client application will display the names of chemicals. For a Spanish-speaker, the application would load the names of chemicals in this way:

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

For a French speaker, the application would load the names using the same query with a different language code:

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

Multiple-instance solution

If you have multiple instances, you can simply treat each of them as if they were a central database. Each instance would have all the data needed by any team, even though only one team would use the instance.

The benefit of this approach is that you don't need to worry about which teams need which data - just give all the data to each of them.

They queries to extract data from the database would be the same. The American team would use the American database to get the English names. The French and Spanish names might not be useful to them, but their presence should do no harm.

You haven't made it clear what data you would consider to be 'unnecessary' in a multiple-instance solution. Do you want to limit the languages available to each user? Are you sure that you will never have a French-speaker working with the American team? Do you want to limit the chemicals available? Does each team work with a strict set of chemicals and never cooperates with other teams?

Can you demonstrate that the share-all approach would be impractical for the amount of data you have?

If you really have so much data, you could provide each team with an empty instance of the database and load only the data that each team requires. Load only the languages spoken by the team, load only the chemicals they study, and load only the names of the chemicals in the spoken languages.

  • to make it more clear for u.. every country medicines will be available in 2 languages: english , country language ... but there is no benefit to have the country medicines in different languages .. or having different medicines from other countries in their software ... the idea of single instance will be perfect for online application and i will need it later .. but for offline software ....mmm it wont be practical – Hatem Ghazy Oct 9 '12 at 3:30
  • @HatemGhazy In that case , modify the insert statement in my answer to load only the English names and the names in whichever other language you need. – Iain Samuel McLean Elder Oct 9 '12 at 12:10
  • so i have now a DB for medicines of only one country ... what about other countries?? – Hatem Ghazy Oct 9 '12 at 13:07
  • @HatemGhazy Set up databases for other countries similarly, loading only the data each country requires. I think I have said all I can to help you with the question you posted. If you have another question about the design of your application, please post a new question. – Iain Samuel McLean Elder Oct 9 '12 at 14:11
  • so now i will have DB for each country contain english reference and native language ...right?? – Hatem Ghazy Oct 9 '12 at 14:20

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.