Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #18139

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

Show all headers | View raw

On 11/9/19 12:58 PM, ^Bart wrote:
> Hi everybody!
> 
> 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
> 
> 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
> 
> 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'd like to use this idea 
> because I could enable a fall back language feature when it's not 
> available an ingredient translation!
> 
> I could have also two different table for recipes, one just to store 
> recipe's name and another one just to store its ingredients!
> 
> I should create PHP code but I'd like to know your opinion!
> 
> Is my idea correct?
> How could I improve my project?
> Should I use tables without id column because the ingredient's name will 
> never change?
> 
> Regards.
> ^Bart

My thoughts for something like this would go as follows:

A recipe has a name, instructions, and a list of ingredients. You don't 
put a 'list of' in the same table as the thing holding the list, and 
ingredients are complicated enough (just by having translations) that we 
want a table of ingredients.

Because of the translation issue, we also need a table of language.

Thus we end up with the following tables:

Language: has a primary key for the id of the language, and the name of 
the language (we could get more complicated, and provide more tables to 
handle translating the name of the language into the various languages)

Recipe: has a primary key for the id of the recipe, and the name and 
directions for the recipe (these could also become translatable similar 
to how we are going to do the ingredients)

Recipe_Ingredient: a many-to-many linking table with foreign keys to a 
Recipe and an Ingredient, might also include columns for quantity, etc.

Ingredient: A table to provide a primary key to link to in 
Recipe_Ingredient, might include a default name for the ingredient, or 
other general information about the ingredient (like how you measure it)

Ingredient_Name: A table to provide the various translations of the name 
of the ingredients, has foreign keys to the Ingredient table and the 
Language table, and the string for name in that language.

We might also have a Recipe_Name and Recipe_Directions table, to provide 
similar translation ability for those parts of the recipe.

The biggest issue with this design is what to do if there isn't an 
ingredient name in the users desired language. Either you have a default 
language that is in the Recipe/Ingredient table (like you did) or 
provide some other fall back (find the ingredient in some other language 
to present to the user).

Back to comp.lang.php | Previous | Next — Previous in thread | Find similar | Unroll thread

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