Re: DB with a main language table and different kind of translations

From	"J.O. Aho" <user@example.net>
Newsgroups	comp.lang.php, comp.databases.mysql
Subject	Re: DB with a main language table and different kind of translations
Followup-To	comp.databases.mysql
Date	2019-11-09 20:33 +0100
Message-ID	<h2oijmFapvoU1@mid.individual.net> (permalink)
References	<qq6un7$c5j$1@gioia.aioe.org>

Cross-posted to 2 groups.

Followups directed to: comp.databases.mysql

Show all headers | View raw

On 09/11/2019 18.58, ^Bart wrote:

Think this one better fits in a database related usegroup like 
comp.databases.mysql

> I'd like to create a db where I should store food recipes with 
> ingredients translations, I thought to create these tables in MariaDB:
> 
> ingredients
> ------------------------
> id_ingredient   name
> 1        sugar
> 2        eggs
> 3        flour
> 
> recipes
> ------------------------
> id_recipe name      FK_id_ingredient
> 1      pasta   2
> 2      pasta   3

You are creating a one to one relation between the ingredients and 
recipes, you need a table in between to join

recipes_ingredients which has two columns, id_recipe and id_ingredient. 
and you remove the FK_id_ingredient (bad naming of the column) as it's 
not needed.

> languages
> -------------------------
> id_language    name
> 1        Italian
> 2        French
> 3        Spanish
> 
> ingredienttranslations
> --------------------------
> FK_id_ingredient FK_id_language name
> 1         1        zucchero
> 2         1        uova
> 3         1        farina

Again, drop the FK_ part of the column names, you want to use the same 
name in all the tables, makes it far easier and in some frameworks you 
will do a lot more if you don't have the same name.

> I thought to create as primary key, in ingredienttranslations table, 
> FK_id_ingredient+FK_id_language.
> 
> The user (I got also a table named users linked to languages!) will see 
> ingredients in his own language but ingredients will be stored in 
> recipe's table in main language (English),

I would suggest you have English as part of your languages in the 
languages table, the ingredients will have a translation_key instead of 
the name. Then you have exactly the same code in your front end to 
display the names regardless of language. Makes your code easier to 
maintain.

> I'd like to use this idea 
> because I could enable a fall back language feature when it's not 
> available an ingredient translation!

Having all languages in a table makes it simple, you just pick the 
languages in the order the user prefers and the fall back language last 
and you just pick the first line you get.

> Should I use tables without id column because the ingredient's name will 
> never change?

A numeric key is better than string based key.

I think Jerry already explained all this once before.

-- 

  //Aho

Thread

DB with a main language table and different kind of translations ^Bart <gabriele1NOSPAM@hotmail.com> - 2019-11-09 18:58 +0100
  Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-11-09 20:33 +0100
    Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-22 02:53 -0800
  Re: DB with a main language table and different kind of translations Arno Welzel <usenet@arnowelzel.de> - 2019-11-09 23:52 +0100
    Re: DB with a main language table and different kind of translations ^Bart <gabriele1NOSPAM@hotmail.com> - 2019-11-10 10:19 +0100
      Re: DB with a main language table and different kind of translations Martin Leese <please@see.Web.for.e-mail.INVALID> - 2019-11-10 10:35 -0700
  Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-25 00:01 -0800
    Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2019-12-25 09:39 -0500
      Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 04:09 -0800
        Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-26 14:01 +0100
          Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 08:28 -0800
            Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-26 19:14 +0100
              Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 10:28 -0800
                Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-26 20:40 +0100
                Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 12:23 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2019-12-26 17:08 -0500
                Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 17:41 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2019-12-27 10:33 -0500
                Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-27 12:55 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2019-12-27 17:51 -0500
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-03 07:14 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2020-03-03 16:39 -0500
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-03 14:43 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2020-03-03 21:39 -0500
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-04 05:35 -0800
                Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2020-03-04 11:11 -0500
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-04 08:17 -0800
                Re: DB with a main language table and different kind of translations Arno Welzel <usenet@arnowelzel.de> - 2020-03-04 08:08 +0100
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-04 05:31 -0800
                Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-26 17:44 -0800
                Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-27 10:55 +0100
                Re: DB with a main language table and different kind of translations robamman2019@gmail.com - 2019-12-27 13:20 -0800
                Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-28 00:21 +0100
                Re: DB with a main language table and different kind of translations kristjanman2k20@gmail.com - 2020-03-03 07:22 -0800
                Re: DB with a main language table and different kind of translations "J.O. Aho" <user@example.net> - 2019-12-27 10:49 +0100
        Re: DB with a main language table and different kind of translations Jerry Stuckle <jstucklex@attglobal.net> - 2019-12-26 10:48 -0500
  Re: DB with a main language table and different kind of translations Richard Damon <Richard@Damon-Family.org> - 2019-12-25 09:36 -0500

csiph-web