> On 19 Feb 2015, at 8:26am, Flakheart <gary.flakheart at gmail.com> wrote:
> 
> 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)
> );

I'll give you another structure which will allow each recipe to be filed under 
many categories, and uses FOREIGN KEYs to enforce correct data-handling.  I'm 
going to try to guess the conventions and style you're using but please excuse 
me if I get it wrong.

CREATE TABLE recipe_data exactly as above

CREATE TABLE category(
     category_id                     INTEGER PRIMARY KEY AUTOINCREMENT,
     category_name           TEXT
);

CREATE TABLE recipes_in_categories(
     r_i_c_id                INTEGER PRIMARY KEY AUTOINCREMENT,
     recipe_id               INTEGER REFERENCES recipe_data(recipe_id),
     category_id                INTEGER REFERENCES category(category_id)
);

Normally for the foreign keys to work you'd have to create indexes on the 
referenced fields.  But since an INTEGER PRIMARY KEY AUTOINCREMENT field is 
automatically indexed this isn't necessary.

Simon.

Simon.

Reply via email to