Proper database table design?

[Fangbeast]

This seems to work for me.

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, "
      
      DatabaseUpdate.s + "UNIQUE (Category, Subcategory, Recordid) ON CONFLICT FAIL)"
      
      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()
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  Structure ChapterInfo
    
    Recordid.s
    
    Chaptername.s
    
  EndStructure
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  NewList ChapterRecords.ChapterInfo()
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  DatabaseQuery.s = "SELECT Chaptername, Recordid FROM Chapters ORDER BY Recordid"
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  If DatabaseQuery(DatabaseHandle.i, DatabaseQuery.s)
    
    While NextDatabaseRow(DatabaseHandle.i)
      
      AddElement(ChapterRecords.s())
      
      ChapterRecords()\Chaptername  = ReplaceString(GetDatabaseString(DatabaseHandle.i,   0), "''", "'")
      
      ChapterRecords()\Recordid     =               GetDatabaseString(DatabaseHandle.i,   1)
      
    Wend
    
    FinishDatabaseQuery(DatabaseHandle.i)  
    
  EndIf
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
  ForEach ChapterRecords.s()
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
    Debug ChapterRecords()\Chaptername
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
    DatabaseQuery.s =  "SELECT * FROM ChapterCategories WHERE Recordid = '"  + ChapterRecords()\Recordid  + "' "
    
    DatabaseQuery.s +  "ORDER BY Recordid, Category, SubCategory"
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
    If DatabaseQuery(DatabaseHandle.i, DatabaseQuery.s)
      
      While NextDatabaseRow(DatabaseHandle.i)
        
        Category.s      = ReplaceString(GetDatabaseString(DatabaseHandle.i,   1), "''", "'")
        
        SubCategory.s   = ReplaceString(GetDatabaseString(DatabaseHandle.i,   2), "''", "'")
        
        If Category.s <> "" And SubCategory.s = ""
          
          Debug "   +--"  + Category.s
        
        ElseIf Category.s <> "" And SubCategory.s <> ""
          
          Debug "      +--"  + SubCategory.s
          
        EndIf
        
      Wend
      
      FinishDatabaseQuery(DatabaseHandle.i)  
      
    Else
      
      MessageRequester("Error", "Failed to return any categories: " + DatabaseError(), #PB_MessageRequester_Ok)
      
    EndIf
    
    ;----------------------------------------------------------------------------------------------
    ; 
    ;----------------------------------------------------------------------------------------------
    
  Next  ; ChapterRecords.s()
  
  ;------------------------------------------------------------------------------------------------
  ; 
  ;------------------------------------------------------------------------------------------------
  
EndProcedure

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

OpenMasterRecipebook()

InsertDummyData()

;SetWindowLongPtr_(GadgetID(0), #GWL_STYLE, GetWindowLongPtr_(GadgetID(0), #GWL_STYLE)|#TVS_TRACKSELECT)

GetDummyData()

__________________________________________________
Spaces added
01.07.2016
RSBasic

[mhs]

I would choose a slightly different scheme for the ChapterCategories table, to avoid duplicate names and for easier handling with sub categories.

Code: Select all

ChapterCategories
  Id INT
  Name TEXT
  ParentId INT
  ChapterId INT

Code: Select all

DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Chickens', 1, NULL)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Boiled',   1,    1)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Roasted',  1,    1)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Grilled',  1,    1)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Pork',     1, NULL)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Wild',     1,    5)")
...

Code: Select all

also a third level would be possible:
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Wild Pork 1', 1, 6)")
DatabaseUpdate(DatabaseHandle.i, "INSERT INTO ChapterCategories (Name, ChapterId, ParentId) VALUES ('Wild Pork 2', 1, 6)")

Pseudo Code for the selection:

Code: Select all

categories = SELECT ChapterCategories WHERE ChapterId = X AND ParentId IS NULL;

foreach categories as category

    debug category.Name
    
    subcategories = SELECT ChapterCategories WHERE ParentId = category.Id

    foreach subcategories as subcategory
        debug subcategory.Name
    next
    
next

btw, you could also combine Chapters and ChapterCategories in this constellation, if you extend the table structure, without duplicate entries...

[Fangbeast]

I would choose a slightly different scheme for the ChapterCategories table, to avoid duplicate names and for easier handling with sub categories.

To be honest, I am having trouble with a lot of this so all and any copious examples are greatly appreciated!!!

__________________________________________________
Quote tag repaired
01.07.2016
RSBasic

[mhs]

Maybe something like that:

Code: Select all

UseSQLiteDatabase()

#db = 0

Structure Category

  Id.i
  
  Name.s
  Description.s
  CreatedOn.i
  
  ParentId.i
  
EndStructure

Procedure.i InsertCategory(Name.s, Description.s, ParentId.i = 0)

  Define.s Query
  Define.i Id
  
  ; === Insert Statement
  Query = "INSERT INTO Categories (Name, Description, CreatedOn, ParentId) VALUES (?, ?, DateTime('now'), ?);"
  
  SetDatabaseString(#db, 0, Name)
  SetDatabaseString(#db, 1, Description)
  SetDatabaseLong  (#db, 2, ParentId)
  
  If Not DatabaseUpdate(#db, Query)
    ProcedureReturn 0
  EndIf
  
  ; === Get the ID of the record
  If DatabaseQuery(#db, "SELECT last_insert_rowid();")
      FirstDatabaseRow(#db)
      Id = GetDatabaseLong(#db, 0)
      FinishDatabaseQuery(#db)       
  EndIf

  ProcedureReturn Id
  
EndProcedure
Procedure   SelectCategory(List Result.Category(), ParentId.i)

  Define.s Query
  
  ClearList(Result())
  
  ; === Select Statement
  Query = "SELECT Id, Name, Description, CreatedOn, ParentId FROM Categories WHERE ParentId = '" + Str(ParentId) + "';"
  
  DatabaseQuery(#db, Query)

  While NextDatabaseRow(#db)
  
    AddElement(Result())
    
    With Result()
      \Id          = GetDatabaseLong  (#db, 0)
      \Name        = GetDatabaseString(#db, 1)
      \Description = GetDatabaseString(#db, 2)
      \CreatedOn   = GetDatabaseLong  (#db, 3)
      \ParentId    = GetDatabaseLong  (#db, 4)
    EndWith
    
  Wend

  FinishDatabaseQuery(#db)

EndProcedure

; === Create Table
If Not OpenDatabase(#db, ":memory:", "", "")
  End
EndIf

DatabaseUpdate(#db, "CREATE TABLE Categories (" +
    "Id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    "Name TEXT, " +
    "Description TEXT, " +
    "CreatedOn INTEGER, " +
    "ParentId INTEGER " +
  ");")

; === Insert Categories in Table
Define.i Id1, Id2

Id1 = InsertCategory("Tricia's Southern Kitchen", "Country cooking at its' finest")

  Id2 = InsertCategory("Chickens", "", Id1)
          InsertCategory("Boiled",  "", Id2)
          InsertCategory("Roasted", "", Id2)
          InsertCategory("Grilled", "", Id2)
    
  Id2 = InsertCategory("Beef", "", Id1)

  Id2 = InsertCategory("Pork", "", Id1)
          InsertCategory("Wild", "", Id2)

Id1 = InsertCategory("Rob's Burgers", "Heart attack food, beware!")

  Id2 = InsertCategory("Seafood", "", Id1)
          InsertCategory("Marinara", "", Id2)
    
  Id2 = InsertCategory("Onions", "", Id1)
          InsertCategory("Boiled",      "", Id2)
          InsertCategory("Caramelised", "", Id2)

; -------------------------------------------------------------------------------

; === Example Select
NewList Chapters.Category()
NewList Categories.Category()
NewList SubCategories.Category()

SelectCategory(Chapters(), 0)

ForEach Chapters()

  Debug "Chapter: " + Chapters()\Name + " - " + Chapters()\Description

  SelectCategory(Categories(), Chapters()\Id)

  ForEach Categories()

    Debug "_Category: " + Categories()\Name
      
    SelectCategory(SubCategories(), Categories()\Id)
  
    ForEach SubCategories()
      Debug "__SubCategory: " + SubCategories()\Name
    Next

  Next
  
Next

This type of construction is ideal for recursive functions... and of course you can add the foreign key constraints...

[Fangbeast]

Can't really understand what you are doing. My problem is going to be doing this from a treegadget later.

I was trying to learn how to load a treegadget from a database and then display it in the tree but now I am panicking and wish I hadn't.

And I'd need to be able to separately add chapters, categories and subcategories later, let people edit them, delete them etc.

Should have just left the program alone, it works:) (maybe clean it up more)

[mhs]

Every Record (Child / Node) is assigend to the superior Record (Parent) with the Id (ParentId). So you have the possibility to add, edit, delete, sort and order every (Child-) Record to every time. It's a type of a parent child relationship 1:n on the same table.

A treegadget can be used like that:

Code: Select all

  Procedure ShowItems(ParentId.i, Level.i)
  
    NewList Categories.Category()
    
    SelectCategory(Categories(), ParentId)
    
    ForEach Categories()
      AddGadgetItem(0, -1, Categories()\Name, 0, Level)
      ShowItems(Categories()\Id, Level + 1)
    Next
  
  EndProcedure

  If OpenWindow(0, 0, 0, 500, 500, "TreeGadget", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  
    TreeGadget(0, 10, 10, 450, 450)                                         ; TreeGadget Standard

    ShowItems(0, 0)
    
    Repeat : Until WaitWindowEvent() = #PB_Event_CloseWindow
    
  EndIf

The Procedure ShowItems() is used for recursion, with the parameter Level the deepness in the tree is set.