CODEWITHSUNDEEP
mysqldatabase-design
jsuissa
jsuissa

Reputation: 1762

Efficient MYSQL DB structure for internationalization

I'm adding support for multiple languages to my db. It has about 10 different tables with various structures. I mapped out the structure below which I believe is a good approach:

Table Languages: 

lang_id (int)
lang_name (varchar 128)

Table Sample 1: 

sample_1_id (int)
name (int -- tran_id)
description (int -- tran_id )

Table Sample 2:

sample_2_id (int)
person (int -- tran_id)
page (int -- tran_id)

Table Translations:

tran_id (int)
lang_id (int)
content (text)

However, to make this work across the board won't the content column in the translations table have to be of type 'text'? If so wouldn't that waste space especially when the majority of the data storied in it is just going to be a few characters? Is there a better way?

Upvotes: 0

Views: 383

Answers (2)

llioor
llioor

Reputation: 6238

What about this... Let's take for example the "pages" table.

Main "pages" table:

id - primary

link

adate

edate

status

etc...

Translate "pages_langs" table:

page_id - the primary one

lang_id - "languages" table for example 1 ** here all what you want to manage for translation **

title - the title of the page for example with lang_id=1

text

term - (the term is for url beautify and stuff like this and needs to be in langs table)

"languages" table:

id - primary

title

prefix

The SELECT will be like this:

SELECT p.id, p.link, pl.title, pl.text 
FROM pages
INNER JOIN pages_langs pl ON p.id = pl.page_id AND pl.lang_id = <<current lang id>>

Any questions?

Upvotes: 0

Ami
Ami

Reputation: 1254

Consider two Translations tables. One like this:

Translations_Short:

tran_id (int)
lang_id (int)
content (VARCHAR(255))

Note that the limit for VARCHAR in MySQL 5.0.3+ is actually 65,535 bytes, though that's also the row limit. So, you could use:

tran_id (int)
lang_id (int)
content (VARCHAR(65522))

And another like this:

Translations_Long:

tran_id (int)
lang_id (int)
content (text)

TEXT is only 65535, so with MySQL 5.0.3+, this is already covered. You may as well use MEDIUMTEXT here.

Then, you can decide per column like this:

sample_1_id (int)
name (int -- tran_long_id)
description (int -- tran_short_id )

Your application will know which one to link to, right?

Anyway, TEXT doesn't really use up much more space than VARCHAR. Maybe one or two bytes. It's performance that's an issue, not space. Since you won't be indexing the TEXT column, that's not likely to be an issue either, unless you were planning on using covering indexes (which wouldn't be possible with TEXT). Indexes for VARCHARs are limited to 767 bytes anyway, so you couldn't use covering indexes with VARCHAR columns over 767 bytes.

But, this would allow you to split them up if you really think it's necessary.

Upvotes: 3

Related Questions