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.