Hi, I'm not an sqlite expert but in general I think you can insert in the tables one by one, but in the right order. And make sure the different inserts per recipe belong to one transaction (if one fails, the previous are rolled back automatically).
You should probably also study https://www.sqlite.org/foreignkeys.html and with the definitions of your foreign keys pay attention to the 'on update cascade ...'and 'on delete cascade ...' gr., Gunnar On 02/18/2015 12:38 PM, Flakheart wrote: > Apologies in advance folks, please forgive the question and the formatting. > > I have around 650,000 recipes and through lots of study have more or less > determined that the table structures below will give me a reasonable > normalised database. > > That is not to say that it is perfect, but it is a start. > > Querying such a thing isn't a problem, I have played with queries to work on > existing normalised databases and 'got the picture'. > > My problem is that: Given all the data on a form from which I gather what to > insert, how would a complex insert statement look to update all the various > tables to keep the relationships. I have no idea how it would look?? Haven't > seen any examples on this, just queries. > > I can deal with single table inserts but I think this would be some sort of > nested insert statement? > > If anyone could help me with an example or at least point me to where I > could see examples of updating normalised databases, I would be grateful. > > Thank you. > recipes table recipe_id ; Unique record number > recipe_name ; Recipe title > recipe_version ; Revision number > recipe_owner ; Recipe owner (home > reference) > recipe_author ; Who wrote the recipe > recipe_source ; Where the recipe was > sourced from > recipe_copyright ; Recipe owner isn't > necessarily copyright holder > recipe_lastupdate ; Last time this was > updated > recipe_favourite ; Marked as a favourite > recipe_deleted ; Marked as deleted, to > be purged > recipe_description ; Specific recipe > description > recipe_notes ; General notes > recipe_servings ; Number of servings > recipe_peparationtime ; Preparation time > recipe_cookingtime ; Cooking time > recipe_totaltime ; Total time > > ingredients table ingredient_id ; > ingredient_name ; > ingredient_price ; > > recipe_ingredients table recipe_id ; > ingredient_id ; > ingredient_amount ; > > recipe_instructions table recipe_id ; > recipe_step ; > step_description ; > > category table category_id ; > category_name ; > > recipe_category table recipe_id ; > category_id ; > > picture table picture_id ; > picture_name ; > picture_url ; > > recipe_pictures table recipe_id ; > picture_id ; > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Complex-insert-query-to-normalised-database-tp80590.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 >
