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

On 9-11-2019 18:58, ^Bart wrote:
> Is my idea correct?

Try to be more sure of yourself....

Do not ask for permission to build some recipe (with translations, and
ingredients) thing.

But, if you need images for your recipes, than visit:


Now, stop asking if you do it correct, or not.

Start building, and LEARN from your mistakes.....

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

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

[..]
> 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.

[..]
> 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.

^Bart:

> 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: [...]
> 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?

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

> 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!

Have a nice day!
^Bart

^Bart wrote:
> Arno Welzel
> 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.

laupäev, 9. november 2019 21:33.16 UTC+2 kirjutas J.O. Aho:
> On 09/11/2019 18.58, ^Bart wrote:
> Think this one better fits in a database related usegroup like
> comp.databases.mysql Exactly my idea!

With the best wishes,
Kristjan Robam
[..]

laupäev, 9. november 2019 19:58.06 UTC+2 kirjutas ^Bart:
[..]
> never change?
> Regards.
> ^Bart

Personally I think you could use some translation tool API to let it translate your words on the run.

With the best wishes,
Kristjan Robam
E-mail: robamman2019x @ aol.com
[Please take off x, when writing to me]

On 11/9/19 12:58 PM, ^Bart wrote:
[..]
> 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).

On 12/25/2019 3:01 AM, robamman2019 wrote:
> laupäev, 9. november 2019 19:58.06 UTC+2 kirjutas ^Bart:
> Personally I think you could use some translation tool API to let it translate your words on the run.

That would add significant overhead compared to a table lookup (as well
as depending on the translation tool site being available at the time).
Additionally, translations are not all that great. There's a reason
good website developers use native speakers of the language they are
translating to instead of tools.

kolmapäev, 25. detsember 2019 16:39.35 UTC+2 kirjutas Jerry Stuckle:
[..]
> Jerry Stuckle
> jstucklex
> ==================
Why then to create translators?
Personally I think, that if you have a native language clients and you are forced to add languages, then you should make a universal application with automatic translator and that is enough. If somebody doubts on the meanings, he/she could look up the most approprite translation from the dictionary hisself or herself.

With the best wishes,
Kristjan Robam
E-mail: robamman2019x @ aol.com
[Please remove x, when e-mailing to me]

On 26/12/2019 13:09, robamman2019 wrote:

> Why then to create translators?
> Personally I think, that if you have a native language clients and you are forced to add languages, then you should make a universal application with automatic translator and that is enough. If somebody doubts on the meanings, he/she could look up the most approprite translation from the dictionary hisself or herself.

For automated translation still has issues to make a text properly
readable, for example



"Even when they saw them marching together, they could not see it. They
even slapped their heads and entered the village office together."

The text is poorly translated and don't make any sense. Sure translating
between related indo-european languages may work somewhat well, but when
you start to translate between language groups, then it's just horribly
wrong.

On 12/26/2019 7:09 AM, robamman2019 wrote:
> kolmapäev, 25. detsember 2019 16:39.35 UTC+2 kirjutas Jerry Stuckle:
> Why then to create translators?
> Personally I think, that if you have a native language clients and you are forced to add languages, then you should make a universal application with automatic translator and that is enough. If somebody doubts on the meanings, he/she could look up the most approprite translation from the dictionary hisself or herself.
> With the best wishes,
> Kristjan Robam
> E-mail: robamman2019x @ aol.com
> [Please remove x, when e-mailing to me]

Translators work when nothing else is available. But they are a poor
excuse for native translations. Try using Google Translate to translate
from one language to another and then back. For instance, translating
"Four score and seven years ago our fathers brought forth onto this
continent" to Traditional Chinese and back to English gives us "Quarters
and seven years ago, our father grew up on this continent".

Hardly the same as the original.

Idioms are even worse.

The purpose of having something available in multiple languages is so
that people don't have to look up the meaning of the words.

Good developers - both web and application - developers understand these
facts and provide translations created by native speakers of that language.

On Thursday, December 26, 2019 at 3:01:22 PM UTC+2, J.O. Aho wrote:
> On 26/12/2019 13:09, robamman2019 wrote:
> > Why then to create translators?
> > Personally I think, that if you have a native language clients and you are forced to add languages, then you should make a universal application with automatic translator and that is enough. If somebody doubts on the meanings, he/she could look up the most approprite translation from the dictionary hisself or herself.
> For automated translation still has issues to make a text properly
> readable, for example I was thinking about this. When you make automated translation, you can make exceptions lists on translation. Automated translating could save a lot of time. Maybe the wrong translations would be about 1%, but this percent isgood enough do do some programming. When checking the translations, you will of course fix the wrong translations to the "exceptional translation list". Otherwise you would be a translator instead of a man/woman called a programmer ;-) :-)

With the best wishes,
Kristjan Robam
E-mail: robamman2019x @ aol.com
(Please remove x, when e-mailing)
[..]

On 26/12/2019 17:28, robamman2019 wrote:
> On Thursday, December 26, 2019 at 3:01:22 PM UTC+2, J.O. Aho wrote:
> I was thinking about this. When you make automated translation, you can make exceptions lists on translation.
> Automated translating could save a lot of time.
> Maybe the wrong translations would be about 1%, but this percent is
> good enough do do some programming.

Word to word translations you may be down at 1% between close languages
as Swedish and Nynorsk (one of the two dialects in Norway), but even a
100% right word to word translation will not give you correct sentences.

> When checking the translations, you will of course fix the wrong translations to the "exceptional translation list".

You will spend more time on that than letting a professional translator
do the job for you, so the translator will be cheaper and makes your
site to look far more professional too.

> Otherwise you would be a translator instead of a man/woman called a programmer ;-)

Rather being a translator than a script kiddie who makes a site loose
it's forging visitor due of bad quality translations.

PS. As you don't use a proper client, delete message footers and adjust
line length according standard manually.