Datenbank richtig aufsetzen und Relationships nutzen

[radi]

Hi Leute,

ich baue mir eine kleine Website, mit der ich über Zutaten und Rezepte meinen Essensplan und den Wocheneinkauf managen möchte. Ja ich weiß, sowas gibt's schon aber ich möchte mich einfach weiterbilden und mir selbst mal sowas aufbauen :)

Dazu habe ich mit Python und Flask mir eine eine Website aufgebaut mit einer Datenbank dahinter. Das klappt auch bisher alles super nur ich habe das Gefühl, dass ich das, was DB eigentlich ausmacht (Relationships und Abhängigkeiten zB), gar nicht richtig nutze.

Ich habe aktuell folgendes DB-Konstrukt (in vereinfachter Form):

Zutat:
- ID
- Name
- Energy_kcal (pro 100g)

Rezept-Zutat:
- ID
- Name
- Zutaten-ID (über foreign key/relationship)
- Menge
- Energy_kcal

Rezept:
- ID
- Rezept-Zutaten-Ids (über foreign key/relationship)
- Portionen
- Energy_kcal

Ich berechne dann in jedem Schritt, wenn ich über die Form-Interfaces ein Objekt erstelle dann auch immer, auf Basis der Ebene darunter, bspw. die Kalorien und gebe diese dem Objekt explizit mit. Das heißt: Wenn ich ein Rezept erstelle, selektiere ich die Zutaten und kombiniere sie mit einer Menge (--> Rezept-item). Dadurch hat die Rezept-Zutat eine mengenspezifische Energie, denn die energieangaben der Zutaten sind, wie es von der Nähwerttabelle bekannt, auf 100g bezogen. dadurch kann ich bspw. (neben anderen Attributen wie Preis, Fett/Carbs in g) angeben, wieviel kcal das Rezept pro Portion hat, denn das Rezept bekommt die Info, für wieviel Portionen noch die Mengen angaben sind. Kenn man alles von Chefkoch oder allen anderen Rezeptseiten.

Jetzt frage ich mich aber, ob das wirklich so gedacht ist, dass ich die Energie (hier als Beispiel) dem Rezept mit einem harten Wert in einer Spalte ablege. Ich verstehe es eigentlich so, dass ich durch die Verknüpfung (über foreign key/relationship) zwischen den Ebenen dann auf die Infos der verknüpften Klassen zugreifen kann. Hier weiß ich dass ein Rezept mit Zutaten verknüpft ist. Das Spiel geht dann auch noch weiter, wenn ich meinen Essensplan weiter aufbauen möchte. Der soll nämlich wie folgt aussehen:

Essensplan >>> Tag >>> Mahlzeit >>> Rezept * Portionen >>> Zutaten für eine Anzahl an Portionen

Am Ende möchte ich z.B. aufschlüsseln können, welche Zutaten (in welcher Menge) ich pro Plan und/oder pro Plan und/oder pro Mahlzeit brauche. Auch möchte ich angeben können was für ein Ernährungsmakro am Ende pro Plan / Tag / Mahlzeit rauskommt, wenn ich die Info Carbs/Fett/Protein über Zutatenwerte betrachte.

Ich denke es sollte eher so sein, dass man sich über die Relationships bspw. von einem Tag aus über Mahlzeit und Rezept so durchhangelt und sich dann am Ende die Werte für bspw Kalorien aus der Zutateninfo rauszieht und nicht schon die Info vorab berechnet hat für den Tag.

So sehen aktuell meine Models aus:
(richtig implementiert ist es bis zur Rezepterstellung)

class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(150), unique=True)
password = db.Column(db.String(150))
first_name = db.Column(db.String(150))
# relations
plans = db.relationship("Plan")
recipes = db.relationship("Recipe")


class Plan(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
date = db.Column(db.DateTime(timezone=True), default=func.now())
# relations
days = db.relationship("Day")
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))


class Day(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))

# relations
meals = db.relationship("Meal")
plan_id = db.Column(db.Integer, db.ForeignKey("plan.id"))


class Meal(db.Model):
id = db.Column(db.Integer, primary_key=True)
portions = db.Column(db.Numeric)
prize = db.Column(db.Numeric)
energy_kcal = db.Column(db.Numeric)
energy_kJ = db.Column(db.Numeric)
fat = db.Column(db.Numeric)
protein = db.Column(db.Numeric)
carbs = db.Column(db.Numeric)
sugar = db.Column(db.Numeric)
type = db.Column(db.String(30))
# relations
recipe = db.relationship("Recipe")
day_ids = db.Column(db.Integer, db.ForeignKey("day.id"))


class Recipe(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(50))
author = db.Column(db.String(50))
duration = db.Column(db.Numeric)
difficulty = db.Column(db.String(50))
instruction = db.Column(db.String(10000))
date = db.Column(db.DateTime(timezone=True), default=func.now())
portions = db.Column(db.Numeric)
# cost and caloric information per portion
cost = db.Column(db.Numeric)
energy_kcal = db.Column(db.Numeric)
energy_kJ = db.Column(db.Numeric)
fat = db.Column(db.Numeric)
protein = db.Column(db.Numeric)
carbs = db.Column(db.Numeric)
sugar = db.Column(db.Numeric)
category = db.Column(db.String(30))
# relations
recipeitems = db.relationship("Recipeitem")
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
meal_ids = db.Column(db.Integer, db.ForeignKey("meal.id"))


class Recipeitem(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
amount = db.Column(db.Numeric)
# prize and caloric information absolut (per Item)
prize = db.Column(db.Numeric)
energy_kcal = db.Column(db.Numeric)
energy_kJ = db.Column(db.Numeric)
fat = db.Column(db.Numeric)
protein = db.Column(db.Numeric)
carbs = db.Column(db.Numeric)
sugar = db.Column(db.Numeric)
type = db.Column(db.String(30))
# relations
ingredient = db.relationship("Ingredient")
recipe_ids = db.Column(db.Integer, db.ForeignKey("recipe.id"))


class Ingredient(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
brand = db.Column(db.String(30))
type = db.Column(db.String(30))
# prize and caloric information absolut (per 100g)
prize = db.Column(db.Numeric)
energy_kj = db.Column(db.Numeric)
energy_kcal = db.Column(db.Numeric)
fat = db.Column(db.Numeric)
carbohydrates = db.Column(db.Numeric)
sugar = db.Column(db.Numeric)
protein = db.Column(db.Numeric)
date = db.Column(db.DateTime(timezone=True), default=func.now())
# relations
recipeitem_ids = db.Column(db.Integer, db.ForeignKey("recipeitem.id"))

Ich mache das nebenher und habe das weder akademisch noch professionell gelernt, sowas in einer echten Datenbank abzubilden, deshalb bin ich mir unsicher wie es richtig geht. Könnt ihr mir da weiterhelfen, damit ich da besser durchsteige, wie man so ein System richtig aufbaut und nutzt?

Ich wäre euch so dankbar!

Viele Grüße,
radi

[Ganon]

Ich verstehe es eigentlich so, dass ich durch die Verknüpfung (über foreign key/relationship) zwischen den Ebenen dann auf die Infos der verknüpften Klassen zugreifen kann.

Datenbank-Technisch kannst du das natürlich. Ob dir dein Objekt-Mapping diese Feature gibt, weiß ich jetzt nicht. Vermutlich kannst du dort auch irgendwelche Abfragen/Queries generieren lassen.

Über SQL-Abfragen würde das über JOIN und SUM gehen. Also z.B. (ohne jetzt das wirkliche Datenmodell zu kennen). Schau da einfach mal in deine Doku zu deinem Framework.


SELECT SUM(i.kcal)*ri.amount AS sum_kcal
FROM recipe_item AS ri
JOIN ingredient AS i ON ri.ingredient_id = i.id
WHERE ri.id = <deine_recipe_item_id>


Das kann man jetzt natürlich noch schachteln wie man will.

[Gast]

Grundsätzlich sollte man beim Design der Datenbank Redundanzen vermeiden.

In deinem Beispiel speicherst du beispielsweise den Energiegehalt sowohl bei Zutat, Rezept, als auch in der Verknüpfungstabelle ab.

Besser wäre es diesen nur 1x zu speichern, und alle anderen benötigten Werte jeweils auszurechnen, das verringert langfristig die Wahrscheinlichkeit von Fehlern.

Sinnvoll wäre hier wohl am ehesten die Energie bei der Zutat zu speichern, und beim Rezept über die Rezept-Zutat-Verknüpfung zu berechnen.

Wobei man das hier evtl. noch weiter aufschlüsseln könnte, beispielsweise indem man bei der Zutat die Anteile an Fett, Kohlehydrate und Eiweiß extra speichert und den Energiegehalt der jeweiligen Komponenten dann in einer eigenen Tabelle.

Ich stelle mir auch die Frage, ob die Abbildung wirklich der Realität entspricht. Wenn ich beispielsweise die Zutat Rumpsteak auf den Griller lege und zubereite rinnt ja bekanntermaßen einiges an Fett heraus, dementsprechend ist der Fett- und damit des Energiegehalt des fertigen Steaks auch niedriger als jener der rohen Zutat.

Man könnte sich also überlegen, nicht einfach nur die die Verknüpfung Rezept-Zutat, sondern auch eine Zubereitungsart zu speichern, inklusive eventueller Anpassungen der Energiewerte.

[radi]

Grundsätzlich sollte man beim Design der Datenbank Redundanzen vermeiden.

In deinem Beispiel speicherst du beispielsweise den Energiegehalt sowohl bei Zutat, Rezept, als auch in der Verknüpfungstabelle ab.

Besser wäre es diesen nur 1x zu speichern, und alle anderen benötigten Werte jeweils auszurechnen, das verringert langfristig die Wahrscheinlichkeit von Fehlern.

Sinnvoll wäre hier wohl am ehesten die Energie bei der Zutat zu speichern, und beim Rezept über die Rezept-Zutat-Verknüpfung zu berechnen.

Wobei man das hier evtl. noch weiter aufschlüsseln könnte, beispielsweise indem man bei der Zutat die Anteile an Fett, Kohlehydrate und Eiweiß extra speichert und den Energiegehalt der jeweiligen Komponenten dann in einer eigenen Tabelle.

Ich stelle mir auch die Frage, ob die Abbildung wirklich der Realität entspricht. Wenn ich beispielsweise die Zutat Rumpsteak auf den Griller lege und zubereite rinnt ja bekanntermaßen einiges an Fett heraus, dementsprechend ist der Fett- und damit des Energiegehalt des fertigen Steaks auch niedriger als jener der rohen Zutat.

Man könnte sich also überlegen, nicht einfach nur die die Verknüpfung Rezept-Zutat, sondern auch eine Zubereitungsart zu speichern, inklusive eventueller Anpassungen der Energiewerte.

Ja genau wegen diesen redundanten Berechnungen habe ich das Gefühl eben diese etablierten Relationships nicht zu nutzen.

Mir fehlt leider noch komplett die Praxis im Umgang mit Datenbanken, sodass ich auch gar nicht weiß wo nach ich genau suchen kann, um die Methoden richtig zu implementieren, da ich genau diese nicht kenne. In den Dokumentationen - bspw hier SQLAlchemy, wird viel mit Abkürzungen gesprochen, was es dem Neuling sehr sehr schwer macht.

Die einzelnen Informationen, die man standardisiert in Nähwerttabellen findet, habe ich implementiert, aber Details, die für mich in diesem Projekt nicht wichtig sind (Salz, Balaststoffe, ungesättigte Fette, ...) habe ich weggelassen. Das hat auch mit diesem Thema jetzt nichts zu tun. Eben so wie eine Diskussion, ob es repräsentativ ist, die nominellen Angaben zu summieren und 1:1 in ein Rezept/Gericht zu überführen. Die diskussion über diese Unschärfen ist zwar interessant, geht aber hier am Thema vorbei und überschreitet auch den Umfang meines Projekts :)

[Ganon]

Ich hab jetzt zwar keine Empfehlung, aber suche dir doch erst mal ein SQL Einsteiger Buch oder Online-Tutorial. Dann verstehst du die Grundlagen und kannst dann auch SQLAlchemy besser verstehen.

[radi]

Da bin ich schon dabei ;)

[ezzemm]

Ich habe MySQL damals mit dieser Seite und den Tutorials gelernt. Hier die Seite zu den Grundlagen der Relationen: https://www.peterkropff.de/site/mysql/relation.htm
Ab hier und den folgenden Seiten geht es darum, was du erzielen willst.

[Gast]

Ja genau wegen diesen redundanten Berechnungen habe ich das Gefühl eben diese etablierten Relationships nicht zu nutzen.


Mit Redundanzen meine ich die Redundante Speicherung von Daten, nicht Berechnungen. Diese sind auch nicht redundant, es genügt für jede dieser Berechnungen genau eine Stelle im Code.

Und genau diese redundante Speicherung der Daten ist grundsätzlich ein schlechtes Design.

Gehen wir mal vom einfachen Beispiel aus die Nährwerte einer Zutat ändern sich. Auch wenn du vielleicht sagts, dass das extrem unwahrscheinlich ist, braucht es sich nur um einen Fehler bei der Dateneingabe handeln.
In dem Fall müsste deine Update Routine nicht nur den Datensatz der Zutat ändern sondern auch den vom Rezept und den von der Speise ändern.
Das ist einerseits Fehleranfällig, wird das nämlich beim Erstellen der Update-Routine vergessen erzeugst du Inkonsistenzen. Andererseits kostest es auch unnötig Performance, da du anstatt nur 1 Datensatz eine ganze Menge ändern musst. Insbesondere wenn mehrere Entwickler zusammenarbeiten, und nicht alle von Anfang an involviert sind und deshalb nicht die genaue Datenstruktur kennen passieren da sehr leicht Fehler.

Auch die eventuelle Erweiterungen der Software werden einfacher, wenn man von Anfang an ein sauberes Design verwendet. Nehmen wir mal an du willst das ganze irgendwann erweitern und nicht nur Nährwerte sondern auch Vitamine und sonstige Spurenelemente mit speichern und auswählen.

In deiner Variante müsstest du dann 3 Tabellen anpassen und dementsprechend auch wesentlich mehr Code der mit diesen Tabellen arbeitet.

Die Normalformen beim Design der Tabellenstruktur für relationale Datenbanken haben durchaus ihre Daseinsberechtigung:
https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)

Diese Regeln sind nicht unbedingt in Stein gemeißelt, und es kann, teilweise aus Performancegründen, durchaus Sinn machen diese zu brechen. Aber wenn man schon eine Regel bricht sollte man einen guten Grund dafür haben, den du bei deiner kleinen Anwendung wohl kaum finden wirst.

Je nach verwendetem DBMs bieten diese auch durchaus Features die ein Abweichen von den Normalformen beispielsweise aus Performancegründen unnötig machen. Beispielsweise calculated fields, oder Views.

[nairune]

Bei sowas immer dazu schreiben, welches Datenbanksystem du nutzt. Je nachdem, ergeben sich verschiedene Lösungsmöglichkeiten.

1. Manche DBMS können computed columns, die eine Funktion o.ä. aufrufen. Da könnte die Berechnung deiner kcal drin stehen.
2. Alle SQL DBMS können Views - deine zweite Option. In den Tabellen stehen die kcal dann nur bei der Zutat und erst in der View wird aufsummiert. Kann natürlich auch mehrere Views geben.
3. Du summierst erst in der Applikation. Das macht wohl nur Sinn, wenn der Wert nur zur Anzeige genutzt wird. Möchtest du hingegen nach Gerichten in einem bestimmten kcal-Bereich suchen können, ist diese Lösung nicht so praktisch.

Bezüglich der Umsetzung mit deinem Framework kann ich dir mangels Erfahrung damit aber leider nicht helfen.