> 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.
