CODEWITHSUNDEEP
mysqlsqldatabaseperformancenormalization
shredding
shredding

Reputation: 5591

Is it more performant to have rows or columns in sql?

If I have to save many strings that are related and that may be dividied in different languages: What's the best way to do it?

I think I have the following options. Option 1 and 3 is the most clear solution to me. They have more columns, but result in fewer rows.

Option 2 and 4 are the most flexible ones (I could dynamically add new string_x without changing the database). They have only three columns but they will result in many rows.

Option 5 would result in many tables.

Option 1:

id | string_1 | string_2 | string_3 | string_4 | ... | string_n | lang

Option 2 *(where name would be string_1 or string_2 etc.)*

id | name | lang

Option 3

id | string_1 | string_2 | string_3 | string_4 | ... | string_n
id | lang     | stringid

Option 4

id | lang | stringid
id | name

Option 5

id | string_1 | lang
id | string_2 | lang
id | ...      |lang

I'm using it to store precached html values for multiple views (one line view, two lines, long description, etc.), if this is of interest.

Upvotes: 0

Views: 211

Answers (4)

Paul Williams
Paul Williams

Reputation: 17020

If you support only a few languages, you might also consider a schema in which each language is its own column:

ID    EN    ES    FR   Etc...

This is less normalized than your option 4, but it is very easy to work with. We have built our database translations like this. As we develop code, we create string resources fill in the English text. Later, a translator fills in the strings of their language.

Upvotes: 0

jayshields
jayshields

Reputation: 405

It depends on a lot of other things. First of all, how many strings could there be? How many languages could there be? To simplify things, let's say if either of those numbers are greater than 5, then options 1 and 3 are infeasible.

Before I go any further, you should definitely look into implementing multi-language functionality outside of the database. In PHP you can use Gettext and put your translation data in flat files. This is a better idea for multiple reasons, the main ones being performance and ease of use with external translators.

If you absolutely must do this in a database then you should use a table structure similar to this:

id | string | language

An example entry would be:

welcome_message | Hello, World! | english

Which I think you've described in Option 2. To clarify, depending on the amount of different languages and different strings, you should use a single table with a fixed number of fields.

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Although I've not had to specifically deal with multi-language interfaces, and if that is all its purpose is, is a translation, I would to option 1, but swapped, something like

id English French German Spanish, etc...

So you would basically have a master column (such as English) as a "primary" word that is always populated, then as available, the other language columns get filled in. This way, you can keep adding as many "words" as you need, and if they get populated across all the different languages, so be it... If not, you still have a "primary" value that could be used.

Upvotes: 0

Guffa
Guffa

Reputation: 700342

Option 1 and 3 are not recommended, as you end up with the language (which is data) in the field name. You have to change the database design if you want to add another language.

Option 5 is not recommended, as you end up with the string identifider (which is data) in the table name. You have to change the database design if you want to add another string.

Option 2 or 4 would work fine. Option 4 is more normalised, as you don't have duplicate string names, but option 2 might be easier to work with if you enter values directly into the table view.

Having many rows in a table is not a problem, that's what the database system is built for.

Upvotes: 5

Related Questions