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.
