Re: Database about food recipes

On 16/06/2019 16.53, ^Bart wrote:
> Hello!
>
> I need to make a db about recipe's ingredients, I should store
> ingredients with more than a language, I'd like to use FK (strong
> connection) and I'd like to have a main language (english) so it can't
> be possible to add ingredients if there isn't an english translation of it!
>
> mainlangtable
> ----------
> ID NAME
> 1  milk
> 2  bread
> 3  water
>
> alllangtable
> -----------------------------
> ID LANG NAME   FK_id_engtable
> 1  ita  latte  1
> 2  ita  pane   2
> 3  ita  acqua  3
> 4  spa  leche  1
> 5  spa  pan    2
> 6  spa  agua   3
>
> recipetable
> ----------------------------
> ID NAME COUNTRYRECIPE  FK_id_mainlangtable FK_id_alllangtable
> 1  xxx  Ita            NULL                1
> 2  xxx  Ita            NULL                3
> 3  xxx  Spa            NULL                4
> 4  xxx  Spa            NULL                6
> 5  xxx  UK             2                   NULL
> 6  xxx  UK             3                   NULL

[Quoted] Link to the "mainlangtable" and skip the "alllangtable", when someone adds something in dutch and it's not in the "mainlangtable", you add the dutch word to the table, notify that the dutch word has to be translated (better to have a flag, bit, to tell if a word ain't translated to english), of course place the dutch word in the the "alllangtable".

[Quoted] Just a question, each recipe will just have one ingredient?

[Quoted] You may want to have a table with the recipe name and id, one table with the translations to different languages and then have your recipe table with id, id_mainlangtable.

[Quoted] I do suggest that you use unique id names, instead of having an id in each table, you use a unique name like ingredient_id for your "mainlangtable" and then you use that name in each table you use it as a foreign key, this will help you a lot when you write your joins, easier to pair columns in the tables.

-- 

  //Aho




> Local people should be able to write their recipes in their local 


> language and foreing people should read them in their language, if there 

> isn't a translation english will be the fallback language.

> 

> I know I could add also another table to link with a FK every languages 

> from alllangtable and recipetable! :)

> 

> Regards.

> ^Bart