The Single or stand-alone table (part 2)

 Welcome back!

In this post I will begin to define the fundamental building blocks of the Database Table from the code side using PureBasic.

A Table is nothing more than a collection of data fields, where each row is a distinct element of the type defined by the Table.  This has nothing to do with the how or where the table is saved. Your table could be a JSON Structure, a standard TXT File, an XML Table or a SQLite table. 

Already we have separated the table from its storage.  This has been completed by the use of the DB Module. So, in your mind separate the storage from the Table or Data.  Here we will be defining the table Data itself. In PureBasic this is BEST Done with a Distinct Model dedicated to an individual structure, or field elements.

I'm going to continue using my Galactic Conquest Game as the source of examples for this series of posts. 

As our Example of a stand-alone table, will use the FighterSize table. Here we define the Fighters which can be carried onboard Carriers.  This table is not a Parent, it is defined simply by the "is a" clause.

The required fields needed are arranged in the structure image below:

  Structure FighterSize
    ID.i                            ; Table Primary Key
    Name.s                          ; FighterSize Name
    UnitDescription.s               ; Short description
    UnitTypeID.i                    ; Foreign Key to Type UnitType
    VisibleWithTechnologyLevelID.i  ; Foreign Key to Table TechnologyLevel
    IsHidden.i                      ; Boolean field
    GameImageID.i                   ; Foreign Key to Table GameImage
    Space.i                         ; the volume of space inside the ship
    ProdCost.i                      ; Cost to Produce
    ProdTime.i                      ; Time it takes to produce in Game turns
    Attack.i                        ; Fighter's attack Value
    Defense.i                       ; Fighter's Definse Value

    State.i
  EndStructure

Good habits, it is always a good habit to provide a short description of the intention the field should be used for. True these may change, but at least you have your original intent. 

I did not provide a value for the field "state" this is NOT a database field, this field is used to carry the "State" of the Record. This is where the DB::#New, DB::#Dirty will be stored.

At this point let's collect our wits and think about what the needed procedure's that a data layer Module must supply for our application. The obvious ones are:

• Find - Retrieve records from the database, by ID (record key), maybe another find-by Name, assuming that your record has a unique name field.
• Insert - The ability to create new records into the database. 
• Update - The ability to change values on existing records.
• Delete - The ability to remove records from our database when we decide they no longer need to be.
• Validate - It only makes sense that we Validate our records before they are saved to the database, and
• DeleteValidate - Again, a procedure to ensure that if this record is deleted, we are not breaking code elsewhere in our application.

The additional procedures rarely pop into the mind when thinking about a data layer, but in our efforts, we are trying to segment ALL database interaction to this one Module for this table; than we would be remis if we did not include loading ComboBox, ListView, and ListIconView Procedures as well.

• LoadComboBox - The Procedure to load the table into a ComboBax Gadget, for use on a form.
• LoadListView - The Procedureto load the table records into a ListView Gadget for use on a form.
• LoadListIconView - The Procedure to load the table into a ListIconView Gadget for use on a form. 

Pausing a moment here it is important to point out that the Loads for ComboBox, and ListView are Single field selection loads. It is logical to think that there may be the need to have multiple of these for a Table. Therefor I propose we modify these procedure Names to include the field we are loading. So, we'll build these for the Name Field.

• LoadComboName - This way the Procedure Name tells you what will; be displayed.
• LoadListViewName - This way the Procedure Name tells you what will; be displayed.

The ListIconView, is different, in that it contains multiple fields into a single Gadget. Aside from cross-reference files I've found that virtually all tables have a ListIconView associated with them.

The Next Modification I propose is to Consolidate the Insert and Update into a Single Save Procedure. Both of these individual Procedure follow the same steps so it's only logical to combine them.

So, this completes our list of "required" Procedures in our Stand-Alone Table Module; and they are:

• LoadComboName
• LoadListViewName
• LoadListIconVire
• Find
• FindByName
• Save
• Delete
• DeleteValidate
• Validate

In Conclusion of this entry, we have now identified the "required" procedures of our Stand-alone Table data late Module. in the next Blogs I will build out each and every one of these Procedures.