[sqlite] Complex insert query to normalised database

[Flakheart]

Here is something I don't understand. From my reading of foreign keys, it
points to the unique id of the table record that it references right? 

If I insert a recipe with a specific category and then a different recipe
that uses the same category, how then does this foreign key work without
storing duplicate categories in the category table?

Then later on, I need a recipe to be a member of multiple categories. I have
no idea how this would work.

I'm at sea here.

CREATE TABLE recipe_data(
  recipe_id                      INTEGER PRIMARY KEY AUTOINCREMENT,
  recipe_name                        TEXT,
  recipe_version                     TEXT,
  recipe_lastupdate          TEXT,
  recipe_favourite                    INTEGER,
  recipe_deleted                      INTEGER,
  recipe_description         TEXT,
  recipe_notes                       TEXT,
  recipe_servings                    INTEGER,
  recipe_peparationtime   TEXT,
  recipe_cookingtime       TEXT,
  recipe_totaltime           TEXT
);

CREATE TABLE category(
      category_id                    INTEGER PRIMARY KEY AUTOINCREMENT,
      category_name          TEXT,
      recipe_id              INTEGER,
      FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id)
);



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590p80623.html
Sent from the SQLite mailing list archive at Nabble.com.