Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 GhazyOct 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 GhazyOct 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 GhazyOct 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 ElderOct 6 '12 at 15:39

2 Answers
2

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:

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:

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:

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.

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 GhazyOct 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 GhazyOct 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 ElderOct 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 GhazyOct 7 '12 at 15:38

@HatemGhazy You can always accept an answer. Click the grey tick under the voting buttons. The tick will turn green to show that you accept the answer. Voting up an answer is different from accepting it. Voting up useful answers is another good practice, but the privilege to vote up an answer requires 15 reputation. Accepting an answer gives you +2 reputation. You currently have 14 reputation, so if you accept the answer, you will have enough reputation to earn the privilege to vote up.
–
Iain ElderOct 7 '12 at 18:51

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.

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 GhazyOct 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 GhazyOct 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 GhazyOct 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 ElderOct 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 CarterOct 8 '12 at 17:10