Database linked records query

[Fangbeast]

My database design consists of recipe data and ingredient data in a linked table.

If I am adding a new recipe, this recipe isn't saved yet, so has no record number. If I want to add ingredients to this unsaved recipe, how do I make sure that they are saved to this recipe when I do save the recipe finally?

Do I create a linked list to keep track of the ingredients or a temporary table that is then stuffed into the ingredients table witht he new record number?

Finding it hard to think in 41 degree heat and fires only 10 suburbs away.

[citystate]

In Sqllist, I usually make an index table like this:

Code: Select all

create table INDX(tablename char,latest integer);
create trigger INDX_alteredtable after insert on alteredtable begin update INDX set latest = NEW.id where tablename='alteredtable'; end;

that way, you just need to make a query to INDX using the table's name to get the last id
of course, you'd need to insert a row on INDX when you create the table, and the table would need to have the ID column named as such

[Fangbeast]

In Sqllist, I usually make an index table like this:

Code: Select all

create table INDX(tablename char,latest integer);
create trigger INDX_alteredtable after insert on alteredtable begin update INDX set latest = NEW.id where tablename='alteredtable'; end;

that way, you just need to make a query to INDX using the table's name to get the last id
of course, you'd need to insert a row on INDX when you create the table, and the table would need to have the ID column named as such

Sorry but I absolutely didn't follow what you did there (and it's not the heat!!)! I used to (years ago) create a temporary table and do a "SELECT INTO" the main table from the temp table but it's hard when you have to keep it synchronised with a visible list for the user as well.

And I can't use the SELECT INTO because I also need to insert the parent record number into the ingredient record.

Looks like I will have to do it the old way until the weather gets reasonable enough for thinking.

[citystate]

the basic idea is to firstly create an index with a column for table name, and one for the last index used
when you create a new table, you insert a row into the index with the table name,
and then create a trigger that updates the index when a row is added to the table - sqllite provides handy pseudo-tables OLD and NEW which let you reference (old and new) values within the trigger.

eg:
{inserts new row into TABLE1, with values id=3, data='stuff'} [trigger listener 'hears' the insert and runs its BEGIN/END loop]
{trigger updates index for table TABLE1 with the value in NEW.id, which is 3 (as that's the new value)}

{inserts another row into TABLE1, with values id=4, data='stuff'} [trigger listener 'hears' the insert and runs its BEGIN/END loop]
{trigger updates index for table TABLE1 with the value in NEW.id, which is now 4}

[captain_skank]

The way i see it there are two options.

1. Save the ingredients to a temporary table - then when you save the recipee, update the ingredients table with the contents of the temporary table and then wipe the temporary table.

2. Save the Recipee automatically when you start adding ingredients - you can always delete it if no ingredients are added.

Option 2 is what i'd go for as it allows for multiple users and no temporary table insanity

[infratec]

Hi,

it depends on your intention.

You can also look at 'TRANSACTION'

Bernd

[thanos]

My database design consists of recipe data and ingredient data in a linked table.

If I am adding a new recipe, this recipe isn't saved yet, so has no record number. If I want to add ingredients to this unsaved recipe, how do I make sure that they are saved to this recipe when I do save the recipe finally?

Do I create a linked list to keep track of the ingredients or a temporary table that is then stuffed into the ingredients table witht he new record number?

Finding it hard to think in 41 degree heat and fires only 10 suburbs away.

Hello.
If i understood correctly, you can use this old trick with a text field:
1. Set a unique value:
e.g.

Code: Select all

sUniqueCode.s = FormatDate("%yyyy%mm%dd%hh%ii%ss", Date()) + Str(ElapsedMilliseconds()) + Str(Random(65536))

2. Save the whole data and store the unique value to the desired field (lets say that is MyRecipeID):

Code: Select all

INSERT INTO table_name (MyRecipeID,column2,column3,...)
VALUES (sUniqueCode,value2,value3,...);

3. Query the database, find the record and retrieve its number:

Code: Select all

DatabaseQuery(0, "SELECT * FROM table_name WHERE MyRecipeID = '" + sUniqueCode + "'")
While NextDatabaseRow(0)
  RecID = GetDatabaseLong(0, DesiredColumn)
Wend

4. Optional: change this text field with a new value if you want (e.g. with 'Recipe #' + RecordNumber)

5. Begin the transaction and make the changes to the other tables.
Regards

Thanos

[Fangbeast]

Sorry to sound dumb but I don't follow the information for triggers or how transaction would help me. Could anyone give me an example of how handle the below two linked tables when a NEW recipe is being created?

Since the recipe is new and not saved, it has no Recordid value yet so I have been using a temporary table to add the ingredients and save them later. Very messy, lots of trouble for me.

I don't understand triggers and I only know enough about TRANSACTIONS to use them for high speed, mass inserts.

Could someone write me a small example based on my tables? I am all at sea here.

The new table may have more than one ingredient added to the new recipe using the temporary table at the moment so I don't know how a trigger can help me.

Code: Select all

CREATE TABLE IF NOT EXISTS Recipes(
Recipetitle TEXT, 
Numberofservings TEXT, 
Recipeauthor TEXT, 
Categories TEXT, 
Subcategories TEXT, 
Preparationtime TEXT, 
Cookingtime TEXT, 
Difficulty TEXT, 
Recipeversion TEXT, 
Recipesource TEXT, 
Copyright TEXT, 
Reciperating TEXT, 
Importedfrom TEXT, 
Authorcomments TEXT, 
Instructions TEXT, 
Nutritionaldata TEXT, 
Othercomments TEXT, 
Deleted TEXT, 
Updated TEXT, 
Favourite TEXT, 
Locked TEXT, 
Recordid INTEGER PRIMARY KEY AUTOINCREMENT, 
      
CREATE TABLE IF NOT EXISTS Ingredients(
Ingredientid INTEGER PRIMARY KEY AUTOINCREMENT, 
Unit TEXT, 
Measure TEXT, 
Ingredient TEXT, 
Preparation TEXT, 
Lineorder TEXT, 
Recordid INTEGER, 
FOREIGN KEY(Recordid) REFERENCES Recipes(Recordid))     

[s0ula55a551n]

I would defo go with the suggestion of creating the recipe record and linking the ingredients to it as you go. Then if they Cancel the recipe just remove the records. It's the easiest way. Or use a list to store things as they are added and then create everything at the. End from the list

[Fangbeast]

I would defo go with the suggestion of creating the recipe record and linking the ingredients to it as you go. Then if they Cancel the recipe just remove the records. It's the easiest way. Or use a list to store things as they are added and then create everything at the. End from the list

I've gone the lazy way because I am just too tired (6 hours straight at the keyboard, 4 days in a row). I just add ingredients to the recipe without saving it and then save ingredients to the recipe if the user saves the recipe.

Because people might want to save the recipe without ingredients and come back to it later.

Thanks for all the suggestions folks.