PureBasic Forums - English

Posted: **Thu Feb 19, 2015 3:19 am**

Looks good Fangles.
The only thing I would change (if it would be my database) to change "recipe_deleted" into "recipe_state".
This way you can have more than two states (deleted/not deleted) but also something like "obsolete" that could be used if something might be on it's way out but not yet deleted.

Take care.

Posted: **Thu Feb 19, 2015 4:52 am**

fsw wrote:Looks good Fangles.
The only thing I would change (if it would be my database) to change "recipe_deleted" into "recipe_state".
This way you can have more than two states (deleted/not deleted) but also something like "obsolete" that could be used if something might be on it's way out but not yet deleted.

Take care.

I understand your point but from my standpoint, my records are either marked deleted or not, nothing else. That field has 1 for deleted, 0 for not.

They are not actually deleted, just marked that way until I select a database purge from my program menu based on that flag. A nice little red line through an item in the list to remind me that I might just still want that record:):)

Posted: **Thu Feb 19, 2015 7:36 am**

Here is the table creation syntax I came up with. Formatting might be wrong here but it looked perfect in my SQL tool.

Now I have to 'cobble up' the syntax for the insert statement to test all of this:):)

When I get time, unless someone wants to help me:):)

Code: Select all

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 owner(
	     owner_id		            INTEGER PRIMARY KEY AUTOINCREMENT,
       owner_name             TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_owner(
	     recipe_id  	          INTEGER PRIMARY KEY AUTOINCREMENT,   	
       owner_id		            INTEGER
);

CREATE TABLE author(
	     author_id		          INTEGER PRIMARY KEY AUTOINCREMENT,
       author_name          	TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_author(
	     recipe_id          	  INTEGER PRIMARY KEY AUTOINCREMENT,   	
       author_id		          INTEGER
);

CREATE TABLE source(
	     source_id		          INTEGER PRIMARY KEY AUTOINCREMENT,
       source_name          	TEXT,
	     recipe_id		          INTEGER,
       FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_source(
	     recipe_id          	  INTEGER PRIMARY KEY AUTOINCREMENT,   	
       source_id		          INTEGER
);

CREATE TABLE copyright(
	     copyright_id		        INTEGER PRIMARY KEY AUTOINCREMENT,
       copyright_name         TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_copyright(
	     recipe_id          	  INTEGER PRIMARY KEY AUTOINCREMENT,   	
       copyright_id		        INTEGER
);

CREATE TABLE ingredients(
	     ingredient_id		      INTEGER PRIMARY KEY AUTOINCREMENT,
       ingredient_name		    TEXT,
       ingredient_price	      TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_ingredients(
	     recipe_id		          INTEGER PRIMARY KEY AUTOINCREMENT,
       ingredient_id		      INTEGER,
       ingredient_amount	    TEXT
);

CREATE TABLE instructions(
	     instruction_id		      INTEGER PRIMARY KEY AUTOINCREMENT,
       instruction_step	      INTEGER,
       step_description	      TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE category(
	     category_id		        INTEGER PRIMARY KEY AUTOINCREMENT,
       category_name		      TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_category(
       recipe_id		          INTEGER,
       category_id		        INTEGER
);

CREATE TABLE picture(
	     picture_id		          INTEGER PRIMARY KEY AUTOINCREMENT,
       picture_name		        TEXT,
       picture_url		        TEXT,
	     recipe_id		          INTEGER,
	     FOREIGN KEY(recipe_id) REFERENCES Recipes(recipe_id)
);

CREATE TABLE recipe_pictures(
	     recipe_id		          INTEGER,
       picture_id		          INTEGER
);

Posted: **Thu Feb 19, 2015 8:10 am**

you can also use integer for

Code: Select all

recipe_lastupdate        TEXT

and use PB-Timelibrary to get a date with time of it....

Look :

Code: Select all

Debug Date()

Posted: **Thu Feb 19, 2015 9:17 am**

Bisonte wrote:you can also use integer for
Code: Select all
recipe_lastupdate        TEXT
and use PB-Timelibrary to get a date with time of it....

Look :
Code: Select all
Debug Date()

I've been doing that for many years, but thank you:):)

Posted: **Fri Feb 20, 2015 2:22 am**

The plan for your database seems to be making progress on its normalizing.

Why do you have 'recipe_id' as a Foreign Key in the tables owner, author, source, copyright, ingredients, instructions, category, and picture?

IMHO it doesn't aid or enable anything useful.

Let's take the category table for instance. It will have entries for different categories, each with a unique primary key. The recipe_category table lists which categories are a part of a given recipe_id. Why would you also have a foreign key in the category table that links each category to a recipe_id?

If there were no recipes that used a category, but it existed in the category table, it would be fine. It would remain as a possible category (if such entries were displayed in a drop-down list during entry of a new recipe for instance). If you wanted to remove it you could search the recipe_category table table for a lack of matching category_ids to determine if you would like to remove it.

I think it would be preferable to remove the foreign key, recipe_id, from each of the tables: owner, author, source, copyright, ingredients, instructions, category, and picture .

Posted: **Fri Feb 20, 2015 6:22 am**

Demivec, it's all very nice but no point, I have absolutely no idea what I am doing with my new structure, much less how to insert anything into this mess.

This is as far as I got and I panicked and just left it all alone.

I need some concrete code examples how to insert data into these messes of tables. Otherwise, normalisation is for the birds:):)

And Idle's sexually deviated sheep!

Posted: **Sun Mar 08, 2015 11:21 pm**

Sorry I didn't see this before!

I've found that much of the time, all that is needed is to create a new index based upon how you do a lookup. For instance, a client of mine had a MySQL database table that was destroying CPU throughput of its server. They were doing a lookup for a particular data, but did not have an index built. The database program then had to read through the entire table to get the data they needed. We added an index for the date, and what had taken about an hour was down to fractions of a second.

What columns are you using for your lookup Fangbeast? Can we create an index for it?

Posted: **Mon Jun 27, 2016 10:25 am**

This is the basic table design. There are other tables but not relevant to my question. I am going to redo the recipe program because it is a mess of added on and badly named procedures and the database just does not scale well after 40,000 recipes. It becomes a dog.

I have created a TreeGadget with Bernd's help that will contain 'Chapters' within the current database (Recipebook) and they will show categories and subcategories under each chapter.

1. How do I link the chapters table to the Recipes table so that each chapter can list its own recipes without creating a table for each chapter?

2. How do I link the ingredients table to the recipes in such a way that each recipe can point to the same ingredient (if it has the same ingredient) without having that ingredient in the ingredients table multiple times?

3. What is the SQL to populate this setup?

; Write the Recipebook chapters

"CREATE TABLE IF NOT EXISTS Chapters("
"Chaptercover BLOB, "
"Chaptername TEXT, "
"Description TEXT, "
"Createdon INTEGER, "
"Ownername TEXT, "
"Copyright TEXT, "
"Homepage TEXT, "
"Recordid INTEGER PRIMARY KEY AUTOINCREMENT)"

; Write the recipe table out

"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, "
"Cuisine TEXT, "
"Reciperating TEXT, "
"Importedfrom TEXT, "
"Authorcomments TEXT, "
"Instructions TEXT, "
"Nutritionaldata TEXT, "
"Othercomments TEXT, "
"Homepage TEXT, "
"Picture TEXT, "
"Deleted BOOLEAN, "
"Updated TEXT, "
"Favourite BOOLEAN, "
"Locked BOOLEAN, "
"Selected BOOLEAN, "
"Shopping BOOLEAN, "
"Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "
"UNIQUE (Recipetitle, Instructions) ON CONFLICT FAIL)"

; Write the ingredients table out

"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) ON DELETE CASCADE)"

Posted: **Mon Jun 27, 2016 4:16 pm**

Fangbeast wrote:1. How do I link the chapters table to the Recipes table so that each chapter can list its own recipes without creating a table for each chapter?

The easiest way will be to add a ChapterID column to the Recipes table as a foreign key to the Chapters table, add this to an appropriate index then add it to the WHERE clause of a SELECT.

Fangbeast wrote:2. How do I link the ingredients table to the recipes in such a way that each recipe can point to the same ingredient (if it has the same ingredient) without having that ingredient in the ingredients table multiple times?

You will have to have an intermediate table which would act as the cross reference between the Recipes table and the Ingredients table.

Something like:-

Code: Select all

  CREATE TABLE IF NOT EXISTS IngredientList(
  RecipeId INTEGER, 
  IngredientId INTEGER, 
  Recordid INTEGER PRIMARY KEY AUTOINCREMENT,
  FOREIGN KEY(RecipeId) REFERENCES Recipes(Recordid) ON DELETE CASCADE,     
  FOREIGN KEY(IngredientId) REFERENCES Ingredients(Recordid) ON DELETE CASCADE,
  UNIQUE (RecipeId,  IngredientId) ON CONFLICT FAIL)

You need to have a think about if this is really necessary though because it adds a whole extra pile of work to the process as you will need to check for each ingredient already existing as you store it, insert it if it doesn't exist, link to the existing one instead if it does exist...

How many ingredients are going genuinely to be duplicated across recipes in view of the fact that you are storing the UoM, preparation and line order in the Ingredients table? Unless you move these values into the IngredientList table in which case you will only be saving storage space on the ingredients name. It will make all the other queries more complex though and I'm not at all convinced at this point that it will be worth the extra effort and might be a normalisation too far...

Posted: **Tue Jun 28, 2016 2:38 am**

You need to have a think about if this is really necessary though because it adds a whole extra pile of work to the process as you will need to check for each ingredient already existing as you store it, insert it if it doesn't exist, link to the existing one instead if it does exist...

Don't know what to do. In my recipe test, I managed to shoehorn over 400,000 recipes into the database before even the program stopped responding (I know, it was an extreme test) but a check of the ingredients revealed over a million or more, can't remember exactly and that's just crazy.

Installed a copy of living cookbook to have a look at how they do it and they reject duplicate ingredients but can't analyse the database because it's password protected.

Damn, still stuck.

Posted: **Wed Jun 29, 2016 5:55 am**

Spikey, how do I save and restore a linked tree from a database consisting of chapter name, category and subcategory that I can write to a treegadget?

For example, a treegadget display might look like this (without the headings of course):

Chaptername: 'Tricia's southern kitchen'
Category: 'Chickens'
SubCategory: 'Southern fried'
SubCategory: 'Super hot'
Category: 'Beef'
Category: 'Pork'
Chaptername: 'Wisconsin sausage cookboook'
Category: 'Fried'
SubCategory: 'Garlic'
SubCategory: 'Chilli sauce'
SubCategory: 'Lean chooken'
Category: 'Steamed'
Category: 'Grilled'

Posted: **Wed Jun 29, 2016 12:50 pm**

Assign each level of indentation a numeric value starting from zero, then you can just select all the value pairs straight out into the tree view. Store an identifying value in the gadget item data so you know what's been clicked.

If you want greater control over the order of appearance than the order in which things were inserted into the database then you could also add a 'sequence' integer to the schema and sort on it in the select statement.

Code: Select all

UseSQLiteDatabase()

DatabaseFile$ = GetHomeDirectory()+"Test.sqlite"

If CreateFile(0, DatabaseFile$) = 0
  Debug "Can't create the database file !"
  End  
EndIf

CloseFile(0)

If OpenDatabase(0, DatabaseFile$, "", "") = 0
  Debug "Can't open database !"
  End  
EndIf

DatabaseUpdate(0, "CREATE TABLE chapters (recordid INTEGER PRIMARY KEY AUTOINCREMENT, level INTEGER, name TEXT);")

DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (0, 'Tricia''s southern kitchen');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Chickens');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Southern fried');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Super hot');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Beef');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Pork');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (0, 'Wisconsin sausage cookboook');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Fried');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Garlic');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Chilli sauce');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Lean chooken');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Steamed');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Grilled');")

If OpenWindow(0, 0, 0, 400, 200, "TreeGadget", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  TreeGadget(0, 10, 10, 380, 180)
Else
  End
EndIf

Count = 0

If DatabaseQuery(0, "SELECT * FROM chapters;")
  
  While NextDatabaseRow(0)
    AddGadgetItem (0, Count, GetDatabaseString(0, 2), 0, GetDatabaseLong(0, 1))
    SetGadgetItemData(0, Count, GetDatabaseLong(0, 0))
    Count + 1
  Wend
  
  FinishDatabaseQuery(0)
EndIf

CloseDatabase(0)

Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow

Posted: **Thu Jun 30, 2016 4:55 am**

I have sort of figured out that what I have to do is have a separate chapters table as it needs extra information in it for each chapter that I don't want duplicated.

Then I have a separate chapter categories table which I can link to the chapter name in one query.

Haven't figured out how to write it yet but don't need to store level numbers as I chapter name is 1, category is 2 and subcategory is 3.

I can get the separate record id from

Code: Select all

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

UseSQLiteDatabase()                                                                                   ; Tell compiler we are using SQLite support

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

Global DatabaseHandle.i, DatabaseName.s                                                               ; 

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

CurrentDirectory.s = GetCurrentDirectory()                                                            ; 
DatabaseName.s     = CurrentDirectory.s + "Stuffaduckelse.db"                                         ; 

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

Declare   OpenMasterRecipebook()                                                                     ; 
Declare.s CountRecords(TableField.s, TableName.s)                                                    ; 
Declare   InsertDummyData()                                                                          ; 

;==============================================================================================================================================================================
; Try to open the system database and create missing tables
;==============================================================================================================================================================================

Procedure OpenMasterRecipebook()
  
  Protected FileHandle.i, DatabaseUpdate.s
  
  FileHandle.i = OpenFile(#PB_Any, DatabaseName.s)
  
  If FileHandle.i
    
    CloseFile(FileHandle.i)
    
    DatabaseHandle.i = OpenDatabase(#PB_Any, DatabaseName.s, "", "", #PB_Database_SQLite)
    
    If DatabaseHandle.i <> 0
      
      ; Write out the chapters table
      
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS Chapters("
      
      DatabaseUpdate.s + "Chaptername TEXT, "
      
      DatabaseUpdate.s + "Description TEXT, "
      
      DatabaseUpdate.s + "Createdon INTEGER, "
      
      DatabaseUpdate.s + "Recordid INTEGER PRIMARY KEY AUTOINCREMENT, "
      
      DatabaseUpdate.s + "UNIQUE (Chaptername))"
      
      If Not DatabaseUpdate(DatabaseHandle.i, DatabaseUpdate.s)
        
        Debug DatabaseError()
        
      EndIf
      
      ; Write the linked categories/subcategories table out. Each chapter can have the same categories and subcategories
      
      DatabaseUpdate.s = "CREATE TABLE IF NOT EXISTS ChapterCategories("
      
      DatabaseUpdate.s + "Categoryid INTEGER PRIMARY KEY AUTOINCREMENT, "     ; This entry's own id
      
      DatabaseUpdate.s + "Category TEXT, "
      
      DatabaseUpdate.s + "Subcategory TEXT, "
      
      DatabaseUpdate.s + "Recordid INTEGER, "
      
      DatabaseUpdate.s + "FOREIGN KEY(Recordid) REFERENCES Chapters(Recordid) ON DELETE CASCADE)"
      
      If Not DatabaseUpdate(DatabaseHandle.i, DatabaseUpdate.s)
        
        Debug DatabaseError()
        
      EndIf
      
    Else
      
      MessageRequester("Database open error", "Could not open database file in database mode.", #PB_MessageRequester_Ok)
      
    EndIf
    
    ; 
    
  Else
    
    ; 
    
    MessageRequester("Database open error", "Could not open or create raw database file.", #PB_MessageRequester_Ok)
    
    ; 
    
  EndIf
  
  ; 
  
EndProcedure

;================================================================================================================================================================================
; Count the number of records in an SQLite database
;================================================================================================================================================================================

Procedure.s CountRecords(TableField.s, TableName.s)
  
  ; 
  
  QueryString.s + "Select COUNT(" + TableField.s + ") AS totalrecs FROM " + TableName.s + ""
  
  ; Get the count of the records in the database
  
  If DatabaseQuery(DatabaseHandle.i, QueryString.s) <> 0
    
    If FirstDatabaseRow(DatabaseHandle.i)
      
      NumberOfRecords.s = GetDatabaseString(DatabaseHandle.i, 0)
      
    EndIf
    
    FinishDatabaseQuery(DatabaseHandle.i)
    
  Else
    
    NumberOfRecords.s = "0"
    
  EndIf
  
  ; 
  
  ProcedureReturn NumberOfRecords.s
  
  ; 
  
EndProcedure

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

Procedure InsertDummyData()
  
  DatabaseUpdate.s = "INSERT INTO Chapters("
  
  DatabaseUpdate.s + "Chaptername, "
  
  DatabaseUpdate.s + "Description, "
  
  DatabaseUpdate.s + "Createdon) "
  
  DatabaseUpdate.s + "VALUES("
  
  DatabaseUpdate.s + "'Tricia''s Southern Kitchen', "
  
  DatabaseUpdate.s + "'Country cooking at its'' finest', "
  
  DatabaseUpdate.s + "1467291007)"
  
  DatabaseUpdate(DatabaseHandle.i, DatabaseUpdate.s)

  DatabaseUpdate.s = "INSERT INTO Chapters("
  
  DatabaseUpdate.s + "Chaptername, "
  
  DatabaseUpdate.s + "Description, "
  
  DatabaseUpdate.s + "Createdon) "
  
  DatabaseUpdate.s + "VALUES("
  
  DatabaseUpdate.s + "'Rob''s Burgers', "
  
  DatabaseUpdate.s + "'Heart attack food, beware!', "
  
  DatabaseUpdate.s + "1467291009)"
  
  DatabaseUpdate(DatabaseHandle.i, DatabaseUpdate.s)
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Chickens', '',            1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Chickens', 'Boiled',      1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Chickens', 'Roasted',     1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Chickens', 'Grilled',     1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Beef',     '',            1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Pork',     '',            1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Pork',     'Wild',        1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Seafood',  '',            1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Seafood',  'Lobster',     1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Seafood',  'Crayfish',    1)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Seafood',  '',            2)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Seafood',  'Marinara',    2)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Onions',   '',            2)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Onions',   'Boiled',      2)")
  
  DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Category, Subcategory, Recordid) VALUES ('Onions',   'Caramelised', 2)")

EndProcedure

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

Procedure GetDummyData()
  
  DatabaseQuery.s = "SELECT m.Chaptername, n.Categoryid, n.Category, n.Subcategory "
  
  DatabaseQuery.s + "FROM Chapters AS m, ChapterCategories AS n "
  
  DatabaseQuery.s + "ORDER BY Chaptername, Category, SubCategory"
  
  ; 
  
  If DatabaseQuery(DatabaseHandle.i, DatabaseQuery.s)
    
    While NextDatabaseRow(DatabaseHandle.i)
      
      Chaptername.s   = ReplaceString(GetDatabaseString(DatabaseHandle.i,   0), "''", "'")
      
      Categoryid.s    =               GetDatabaseString(DatabaseHandle.i,   1)
      
      Category.s      = ReplaceString(GetDatabaseString(DatabaseHandle.i,   2), "''", "'")
      
      SubCategory.s   = ReplaceString(GetDatabaseString(DatabaseHandle.i,   3), "''", "'")
      
      Debug "Chapter name: " + Chaptername.s + ", Category: " + Category.s + ", SubCategory: " + SubCategory.s
      
    Wend
    
    FinishDatabaseQuery(DatabaseHandle.i)  
    
  Else
    
    MessageRequester("Error", "Failed to return any categories: " + DatabaseError(), #PB_MessageRequester_Ok)
    
  EndIf
  
  ; 
  
EndProcedure

;==============================================================================================================================================================================
; 
;==============================================================================================================================================================================

OpenMasterRecipebook()

InsertDummyData()

GetDummyData()

Posted: **Fri Jul 01, 2016 7:44 am**

Spikey, I am stuck

Code: Select all

DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (0, 'Tricia''s southern kitchen');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (1, 'Chickens');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Southern fried');")
DatabaseUpdate(0, "INSERT INTO chapters (level, name) VALUES (2, 'Super hot');")

This list is good for reading out once only and displaying but later on, when I want to add, edit, delete elements, there is no relationship between them (Chapter, category, subcategory, level etc).

Is there a better way to store these items in the database with a relationship? The chapters have to be in a separate table as they contain extra info and I didn't want duplication.

PureBasic Forums - English

Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?

Re: Proper database table design?