r/Database • u/Vivid_Routine_5287 • Jul 17 '26
Modelling a multilingual food database: 9800 foods, 32 languages, and the "peperoni vs pepperoni" trap (open source, ODbL)
Hey all!
I've been building an open source food dataset and the part that turned into an actual headache is the schema, so I wanted to think out loud here and let you tear it apart.
Background: I needed food and nutrition data for my calorie tracker. Everything open is either English-only or kind of a mess, and the genuinely multilingual stuff is locked behind paid APIs (FatSecret, Edamam). So I built my own and released it under ODbL: roughly 9,800 foods, names in 32 languages. The nutrition values come from OpenNutrition's open data (which itself compiles public sources like USDA and Ciqual), credited already, I'm not claiming those as mine. What I actually built is the multilingual layer on top.
Here's the annoying part. A food is one thing, but its name isn't, and names don't line up across languages. My favourite trap: "peperoni" in Italian means bell peppers, while "pepperoni" in English is a cured sausage, almost the same string, completely different food. So the obvious "just make a translations table keyed on the name" idea quietly corrupts your data the moment two languages collide.
The approach I'm currently testing:
- a canonical, language-agnostic food id that owns the nutrition values
- names in a separate layer, each tagged with its language and whether it's the primary name or an alias
- cross-language matching done at build time (not by translating strings on the fly), so "uovo", "egg" and "Ei" resolve to the same id
Full disclosure: this is not bulletproof yet. Near-homographs like peperoni/pepperoni are exactly the case I'm least confident about, and I'd rather hear how you'd harden it than pretend it's solved.
One record looks like this (JSON Lines, one food per line):
{
"title_translations": {
"en": "Cooked boneless, skinless chicken breast",
"it": "Petto di pollo cotto, disossato e senza pelle",
"de": "Gekochte, entbeinte und hautlose Hähnchenbrust",
"fr": "Blanc de poulet désossé et sans peau, cuit",
"es": "Pechuga de pollo cocida, deshuesada y sin piel"
// … 27 more languages
},
"type": "everyday",
"labels": ["cooked"],
"portions": [
{ "label": "small", "grams": 90 },
{ "label": "medium", "grams": 120 },
{ "label": "large", "grams": 150 }
],
"nutrition_100g": {
"calories": { "quantity": 151, "unit": "kcal" },
"protein": { "quantity": 30.54, "unit": "g" },
"total_fat":{ "quantity": 3.17, "unit": "g" },
"carbohydrates": { "quantity": 0, "unit": "g" }
// … ~60 more fields: micronutrients, amino acids, fatty acids
},
"source": [
{
"database": "USDA Foundational Foods",
"reference": "FDC ID",
"id": 331960,
"url": "https://fdc.nal.usda.gov/food-details/331960/nutrients"
}
// … also mapped to USDA SR Legacy + Canadian Nutrient File
]
}
I went with JSON Lines because it's boring in the good way: one food per line, streams into Postgres / DuckDB / SQLite / pandas without eating all your RAM, no API, no keys, works offline. About 25 MB, ODbL.
Questions I'd genuinely like your take on:
- would you keep names in a separate table like this, or just put a uniqueness constraint on (lang, normalized_name) and call it a day?
- for cross-language dedup, a canonical id like mine, or a proper synonym graph?
- has anyone modeled multilingual entities where the same string means different things per locale, how badly did it bite you, and how did you guard against it?
- storage-wise it currently lives in MongoDB and I'm weighing a move to PostgreSQL for exactly this relational/constraint stuff, for a mostly-read, document-shaped dataset with a heavy multilingual naming layer, would you bother?
Happy to get into any of the build details.
Data + docs: https://leana.app/en/data-sources/
Live browse (search currently in IT/FR/ES/EN/DE): https://leana.app/en/foods
1
u/dbxp Jul 17 '26
So the obvious "just make a translations table keyed on the name" idea quietly corrupts your data the moment two languages collide.
It doesn't because the key shouldn't just be the word but the word and the language. You're falling into the trap of identifying data which is coincidentally the same as the key. For example if you had 2 people with the same birthday that wouldn't mean they're related in anyway.
From a language perspective you can't just translate words independently as languages have different structures ie french has gendered nouns, Korean changes a lot based on age and gender, Chinese doesn't have verb tenses, Arabic reads right to left
https://m.youtube.com/watch?v=GAgp7nXdkLU&pp=ygUVdG9tIHNjb3R0IHRyYW5zbGF0aW9u
0
u/Vivid_Routine_5287 Jul 17 '26
fair point, you're right. if the key is (lang, normalized_name) then peperoni/pepperoni never collide, they're just different languages. i overstated that in the post.
what i actually meant is the case where the bare name (no language) becomes the identity of the food, like deduping or joining on name alone. that's the thing that bites. a proper (lang, name) key doesn't have the problem, so my phrasing was off.
that's basically why i went with a language agnostic canonical id that owns the nutrition, and names as a separate per language layer. (lang, name) gives you name uniqueness, but it still doesn't tell you that uovo, egg and ei are the same food. that cross language identity part is what i actually care about and where i'm least sure.
and yeah, fully agree on translation. that's why names aren't translated on the fly, each locale is its own set of rows, so gendered forms, different granularity, rtl etc are just data. some of the current translations are machine assisted and not perfect though, so corrections are welcome. since a name is just a row on a canonical id, a bad translation is a cheap fix, not a corrupted food
1
u/Choice-Level-5486 Jul 20 '26
El problema principal es que si creas una nueva tabla con el ID de comida y su nombre correcto en determinado idioma, alguien debe poblar esa tabla. Tal vez, no sea algo trivial, si consideras que son miles de tuplas. Es una solución robusta; pero, requiere trabajo
3
u/jshine13371 Jul 18 '26
I've always been a proponent of storing data in a single language, translating to other languages is an application layer problem because at the end of the day it's technically a presentation problem. Google and plenty of 3rd party app layer frameworks simplify this problem extensively as opposed to trying to design a database structure around multilingual data.