reinnovate database

The Actual Database – Part 2

Tables of Single Cells and Multiple Cells

In a worksheet, there are single occurrence cells and multiple occurrence cells as well. All single cells are using one database table to store the data while each set of multiple cells are using another database table. Take the example of the default worksheet Sheet1, all single cells are associated with the table “a”, while the first set of multiple cells are associated with the table “a1” and the second set of multiple cells are associated with the table “a2”.
single cell associated with table a, multi cell associated with table a1, a2

It’s common for a worksheet to be associated with more than one table when multiple value fields are used. In this case, the Key Cell, or Key Field, is the common field that links up the records of an entry in different tables. The Key Field is also the unique identifier, or Entry Number, of a worksheet.
In the example of the default worksheet Sheet1, the key field is DocNo and it is the common field in all the tables “a”, “a1” and “a2” (i.e. a.DocNo, a1.DocNo and a2.DocNo) to relate the records of each of the tables in Sheet1.
the key field in Sheet1 is DocNo
We can have a look at the tables using DB Tables. In Main Menu, click “DB Tables” in the left panel:
click db tables to view the database tables
key field DocNo in table a, a1, a2
The key fields in different tables hold the same values of the unique identifier, or entry number, of the record entries in the worksheet and this is the way BAU DB relates the tables of single cells and multiple cells together inside a single worksheet.
In this example, since table “a” is the main table for the single value cells, there must be only one row of record for each of the entry number. There is only one “A000001”, “A000002”, … etc in the main table “a”, while there can be multiple records with same entry number in table “a1” and “a2”, since they are tables for multiple value cells.

Define Key Table, Field and Index

The key field of a worksheet is defined in Worksheet Setting. Click “Worksheet Setting” in Main Menu and select “Sheet1”. The key table and field is defined in the cell “Key Table.Field” and the main index is defined in the cell “Main Index”. The value of main index must be “idx” + key field name.
definition of the key table and field
Indexes are some internal sorted lists of data in the database tables, which serve to help finding data faster. There are some pages that help you know more about index:
What’s an Index, How do Indexes work, Index (IDX)
In Input Pane’s Cell Properties, the key cell is only defined in the key table, or the table of single value cells. There is no need to define key cell for the tables of multiple value cells. When “Update DB Tables” button is pressed, the key field will be created automatically for these tables.
define key field in input pane's cell properties
No need to define key field for the tables of multiple value cells

Use Your Own Database Tables and Fields

BAU DB is very convenient such that you can create a ready-to-use worksheet in a single click. At the same time, BAU DB is also very flexible and allows you to create a worksheet from scratch, using your own database tables and fields in the worksheet.
The advantage of using your own table and field names is that you can use some meaningful words for the table and field names, so that you can apply formulas in a much easier manner, without the need to figure out what the data fields are referring to. Tables and fields are defined in Input Pane’s Cell Properties :
define table and field in Input Pane's Cell Properties
In the default worksheet Sheet1, all fields are using data type of “Text” with length of 255 characters. You can also choose your own data types for the data fields. Choosing proper data types for the data fields will be more convenient for the calculation in formula, sorting in data list and formatting of the data.
define data type for the database field in Input Pane's Cell Properties
Whenever any tables, fields or data types are changed, save the worksheet setting and press “Update DB Tables” to apply the changes to the actual database file.
Press Update-DB-Tables to apply the changes to the real database file.