From Newsgroup: comp.databases.mysql

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-a-a name
1-a-a-a-a-a-a-a sugar
2-a-a-a-a-a-a-a eggs
3-a-a-a-a-a-a-a flour

recipes
------------------------
id_recipe name-a-a-a-a-a FK_id_ingredient
1-a-a-a-a-a pasta-a-a 2
2-a-a-a-a-a pasta-a-a 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-a-a-a name
1-a-a-a-a-a-a-a Italian
2-a-a-a-a-a-a-a French
3-a-a-a-a-a-a-a Spanish

ingredienttranslations
--------------------------
FK_id_ingredient FK_id_language name
1-a-a-a-a-a-a-a-a 1-a-a-a-a-a-a-a zucchero
2-a-a-a-a-a-a-a-a 1-a-a-a-a-a-a-a uova
3-a-a-a-a-a-a-a-a 1-a-a-a-a-a-a-a 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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

I'm sorry, you're right!

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

Do you mean something like this:

ingredient
-------------------
id_ingredient name
1 sugar
2 eggs
3 flour

recipes
------------------
id_recipe name
1 pasta "X"

ingredientrecipes
-------------------
id_ingredientrecipe id_ingredient id_recipe
1 2 1
2 3 1

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.

Ok, in my mind it helps to show me it's a FK!

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.

Ok!

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.

[CUT]

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.

I should replace the tables ingredient and ingredienttranslations just
with one table where I should store all translation ingredients:

languages
------------------
id_language name
1 english
2 italian
3 french
4 spanish

ingredients
-------------------------
id_ingredients name id_language translation_key
1 eggs 1 1
2 uova 2 1
3 flour 1 3
4 farina 2 3

But in this case it's more complicated to enable the add feature just if
you add an ingredient in english before because just after this step
you'll be able to connect a translation to the languages needed!

If I have a table with all english ingredients I must add other
ingredients in the ingredienttranslation table just if there's a key to
the english version without to write a particular code!

Or maybe I didn't understand what you said... :\

A numeric key is better than string based key.

I think Jerry already explained all this once before.

Yes of course but... if this is the best practice I don't understand
why, also in other newsgroup, someone would like to drop id column and
replace it with two primary key formed by string field!

I think the best answer could be "to drop id column could be a good
solution but you'll have bad performance than to have an id column!",
just Jerry Stuckle said something like this...

Regards.
^Bart

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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

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

I'm sorry, you're right!

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

Do you mean something like this:

ingredient
-------------------
id_ingredient name
1-a-a-a-a-a-a-a-a-a sugar
2-a-a-a-a-a-a-a-a-a eggs
3-a-a-a-a-a-a-a-a-a-a-a-a flour

recipes
------------------
id_recipe name
1-a-a-a-a-a-a-a-a pasta "X"

ingredientrecipes
-------------------
id_ingredientrecipe id_ingredient id_recipe 1-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a 2-a-a-a-a-a-a-a-a-a-a-a-a 1 2-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a 3-a-a-a-a-a-a-a-a-a-a-a-a 1

Almost, drop the id_ingredientrecipe, there is zero use of it. and you
want to make (id_ingredient, id_recipe) to the primary key. You may want
to have column for the amount of the ingredient.

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.

Ok, in my mind it helps to show me it's a FK!

That you will see when you do a "describe ingredientrecipes;", also the assumption is that columns with the same name in a table which don't
contain information but a reference would be foreign keys.

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.

[CUT]

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.

I should replace the tables ingredient and ingredienttranslations just
with one table where I should store all translation ingredients:

languages
------------------
id_language name
1-a-a-a-a-a-a-a english
2-a-a-a-a-a-a-a italian
3-a-a-a-a-a-a-a-a-a-a french
4-a-a-a-a-a-a-a-a-a-a spanish

ingredients
-------------------------
id_ingredients name-a-a-a-a id_language translation_key 1-a-a-a-a-a-a-a-a-a-a-a-a-a eggs-a-a-a-a 1-a-a-a-a-a-a-a-a-a-a 1 2-a-a-a-a-a-a-a-a-a-a-a-a-a uova-a-a-a-a 2-a-a-a-a-a-a-a-a-a-a 1 3-a-a-a-a-a-a-a-a-a-a-a-a-a flour-a-a-a 1-a-a-a-a-a-a-a-a-a-a 3 4-a-a-a-a-a-a-a-a-a-a-a-a-a farina-a-a 2-a-a-a-a-a-a-a-a-a-a 3

languages
language_id name

translation_keys
translation_key_id translation_key

translations
language_id translation_key_id text

ingredients
ingredient_id translation_key_id


The translations table can hold any type of text, ingredient, recipes or
free text that you use in other part of the site.

But in this case it's more complicated to enable the add feature just if
you add an ingredient in english before because just after this step
you'll be able to connect a translation to the languages needed!

The only complication here is that you need to do a few more inserts
when you add something. For the automated part it don't matter if the translation_key is in language X or Y.

No matter what you do, you will have a complication with allowing others
add what they want, as you will not be able to match muna with |ngg
unless you only allow them to translate from a predefined language like english, or you force them to always have the english translation.

Alternative is to a word table, you then have the id, name, and the
language columns, then you have another table where you have meaning
with an id and description. Then you have a table for joining the
meaning and the word, but you can't auto add anything to this, you have
to have someone to match the words together.

If I have a table with all english ingredients I must add other
ingredients in the ingredienttranslation table just if there's a key to
the english version without to write a particular code!

And if someone wants to add pU+peepeo and you don't have mirin in your english list?

Or what if you have mirin in your english list but no translations and
the person who writes the recipe don't know English (quite common in
Japan), then how do they add pU+peepeo?

You are to English fixated, think about your whole project was in
Japanese and you would have to add English. Translations will never be
easy specially if you allow user input.

Or maybe I didn't understand what you said... :\

A numeric key is better than string based key.


I think Jerry already explained all this once before.

Yes of course but... if this is the best practice I don't understand
why, also in other newsgroup, someone would like to drop id column and replace it with two primary key formed by string field!

For they are less skilled than Jerry.

I think the best answer could be "to drop id column could be a good
solution but you'll have bad performance than to have an id column!",
just Jerry Stuckle said something like this...

Se, he has the experience, so you should listen to him.
--

//Aho
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

^Bart wrote:

Arno Welzel

What does this have to do with PHP? Why do ask other people to do your
homework?

Thanks for your reply, I don't need and I don't want someone who work
for me, I'm a php+mysql newbie and sometimes I have wrong ideas, this is
a newsgroup and I'm here just to ask other opinions!

Usually I help other people in other n.g. or forum (for example about Debian) and I never replied to someone like you did with me...

I know if I create a bad db I'll have problems when I'll write php code
for this reason I posted here my doubts!

What is going on, here? There are numerous
mysql newsgroups, including
comp.databases.mysql. When I look over
there, I see a thread with the same Subject.
This is because J.O.Aho added
comp.databases.mysql to the list of
newsgroups with it set as the Followup-To.
He even asked you to take this over there;
look:

J.O. Aho wrote:

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

Please take this over to
comp.databases.mysql, where you have already
posted (although you are presumably unaware
of this.) I have set the Followup-To of
this post to encourage you.
--
Regards,
Martin Leese
E-mail: please@see.Web.for.e-mail.INVALID
Web: http://members.tripod.com/martin_leese/
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

Almost, drop the id_ingredientrecipe, there is zero use of it. and you
want to make (id_ingredient, id_recipe) to the primary key. You may want
to have column for the amount of the ingredient.

Ok, I could make a unique key with id_ingredient+id_recipe but I think
also in this case it could have an autoincrement column
(id_ingredientrecipe) because it could be more fast to have it than to
don't have it...

That you will see when you do a "describe ingredientrecipes;", also the assumption is that columns with the same name in a table which don't
contain information but a reference would be foreign keys.

Yes, you're right!

languages
language_id name

languages
-----------------------
language_id name
1 Italian
2 French
3 Spanish
4 English

translation_keys
translation_key_id translation_key

translation_keys
-------------------------------------
translation_key_id translation_key
1 uova
2 eggs
3 huevos
4 flour

translations
language_id translation_key_id text

translations
-------------------------------
language_id translation_key_id text
4 1 eggs
1 2 uova
1 3 uova
2 4 farine

ingredients
ingredient_id translation_key_id

ingredients
--------------------------------------
ingredient_id translation_key_id
1 1
2 2

The translations table can hold any type of text, ingredient, recipes or free text that you use in other part of the site.

I'm sure I didn't real understand your idea, please could you correct my table's mistakes? :\

The only complication here is that you need to do a few more inserts
when you add something. For the automated part it don't matter if the translation_key is in language X or Y.

Ok, but like what I wrote above I think I didn't understand your idea...

No matter what you do, you will have a complication with allowing others
add what they want, as you will not be able to match muna with |ngg
unless you only allow them to translate from a predefined language like english, or you force them to always have the english translation.

I have a table with both ingredients in english and their translations
in other four languages so I will not allow the end user to add
something about it, I thought to have all ingredients slave to the
english translation.

Alternative is to a word table, you then have the id, name, and the
language columns, then you have another table where you have meaning
with an id and description. Then you have a table for joining the
meaning and the word, but you can't auto add anything to this, you have
to have someone to match the words together.

This is the reason why I created a table with all ingredients in english language and another table with ingredients in other languages connected
(FK) to the main english table.

And if someone wants to add pU+peepeo and you don't have mirin in your english list?

In english list I could have "Rice wine".

Or what if you have mirin in your english list but no translations and
the person who writes the recipe don't know English (quite common in
Japan), then how do they add pU+peepeo?

If someone has a "Rice wine" (Mirin or something else) he'll connect his ingredient to the right english meaning, it could be a good idea to add
a local name close to the translation for example Rice wine (Mirin).

You are to English fixated, think about your whole project was in
Japanese and you would have to add English. Translations will never be
easy specially if you allow user input.

Like what I said I'll add every translations and if someone need to add something he will write to the admin by the online form, I'm not fixated
to the English language but thanks to it I could connect every language
to a single language and I could be sure to have a fallback language in
the most famous language in the world!

If "uova" is signed as italian and it's connected to eggs, if huevos is
signed as spanish and it's connected to eggs, every ingredient's recipe
are stored in the recipe's table in english it'll be easy to have a translation to every languages.

Se, he has the experience, so you should listen to him.

Experience is better than words and words and I should say thanks to
people like you and him!

Regards.
^Bart

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

On 13/11/2019 18.55, ^Bart wrote:

Almost, drop the id_ingredientrecipe, there is zero use of it. and you
want to make (id_ingredient, id_recipe) to the primary key. You may
want to have column for the amount of the ingredient.

Ok, I could make a unique key with id_ingredient+id_recipe but I think
also in this case it could have an autoincrement column (id_ingredientrecipe) because it could be more fast to have it than to
don't have it...

That you will see when you do a "describe ingredientrecipes;", also
the assumption is that columns with the same name in a table which
don't contain information but a reference would be foreign keys.

Yes, you're right!

languages
language_id name

languages
-----------------------
language_id name
1-a-a-a-a-a-a-a-a-a-a Italian
2-a-a-a-a-a-a-a-a-a-a French
3-a-a-a-a-a-a-a-a-a-a Spanish
4-a-a-a-a-a-a-a-a-a-a English

translation_keys
translation_key_id translation_key

translation_keys
-------------------------------------
translation_key_id translation_key
1-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a uova
2-a-a-a-a-a-a-a-a-a-a eggs
3-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a huevos 4-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a-a flour

No, this iw wrong, you can't have myltiple translation key for the same
thing.

translation_key_id translation_key
1 ingredient_uova
2 ingredient_flour

translations
language_id translation_key_id text

translations
-------------------------------
language_id translation_key_id text
4-a-a-a-a-a-a-a 1-a-a-a-a-a-a-a-a-a-a-a-a-a-a eggs
1-a-a-a-a-a-a-a 1-a-a-a-a-a-a-a-a-a-a-a-a-a-a uova

ingredients
ingredient_id translation_key_id

ingredients
--------------------------------------
ingredient_id translation_key_id
1-a-a-a-a-a-a-a-a-a 1
2-a-a-a-a-a-a-a-a-a 2

The translations table can hold any type of text, ingredient, recipes
or free text that you use in other part of the site.

I'm sure I didn't real understand your idea, please could you correct my table's mistakes? :\

You treat all languages the same, English ain't different from any other language, to get the English text you have the same SQL as for getting
your Mongolian text.

The translation key is not the english (or any other language), it's for
you to find a text in a fast way, say the text ain't just a word but a paragraph of text, which you will have part of your site, say we have
your PHP code and you created the function getTransalation which takes
to arguments Language and TranslationKey

getTransalation($UserSelectedLanguage, 'DESC_HOW_TO_ORDER');

And if someone wants to add pU+peepeo and you don't have mirin in your
english list?

In english list I could have "Rice wine".

Or what if you have mirin in your english list but no translations and
the person who writes the recipe don't know English (quite common in
Japan), then how do they add pU+peepeo?

If someone has a "Rice wine" (Mirin or something else) he'll connect his ingredient to the right english meaning, it could be a good idea to add
a local name close to the translation for example Rice wine (Mirin).

You lost the point, the person do not know English, you will have an
issue and you will need someone who can translate the persons request
from their local language to English and you hope that they do not
translate u+aoaA and pU+peepeo both to "Rice wine".
--

//Aho

--- Synchronet 3.21d-Linux NewsLink 1.2