(And if you go for the one to many in between table, then you shouldn't add the recipe_category_id field to recipe-data and also not the foreign key).
An alternative is when you say that one recipe_data can belong to at most, say, 5 categories. Then you can do without the extra table and add recipe_category_id1, recipe_category_id2, ... recipe_category_id5 fields to recipe_data. And 5 foreign keys. On 02/19/2015 10:08 AM, gunnar wrote: > You should make the relation the other way around. > Remove the foreign key from category and add to recipe_data a field > recipe_category_id and add also to the recipe_data table a foreign key > > FOREIGN KEY(recipe_category_id) REFERENCES category(category_id) > > > Then you have defined a 1 to many relationship (each recipe_data > belongs to at most one category and each category can belong to many > recipe_data's). > > > If you want a many to many relationship (which you want from what I > read) then you need one extra table in between with two fields > > recipe_data_category > recipe_id > category_id > FOREIGN KEY(recipe_id) REFERENCES recipe_data(recipe_id) > FOREIGN KEY(category_id) REFERENCES category(category_id) > > > something like that. > > > Gunnar > > > > > > > > On 02/19/2015 09:26 AM, Flakheart wrote: >> 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. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >
