A database is made up of three types of nodes.
The database itself is an instance of the standard Database node type.
The database contains tables. Each table is an instance of the standard Table node type.
Each table contains multiple rows. Each row is an instance of a row type. The row type is created using the Row Type node type. The row type defines the fields used on the table. The row type for a table is defined on the table.
You can use any fields you like within the row. However, since the point of the local database is to have a shareable body of data, it is best if you stick to a normalised model with simple fields that have either just a text part, just a number part, or a boolean, without repeating fields. This means that the data can be more readily interpreted in other systems. The following are recommended:
- Text Field for single-line text. Where the text is used to name the row, use Name Field.
- Text Block Field for blocks of text, optionally including HTML markup.
- Text Drop-Down Field or Text Radio Field for choosing from one of a number of options.
- Number Field for numbers.
- Date Field for dates.
- Boolean Field for booleans.
Set the short reference of each field to the name by which you want it to be known in the database, similar to its column name in a conventional database.
Creating the row type
Add your fields to the member type list of row type.
In the Tab/seq column, you can:
- Optionally, set a tab reference before a /, as in the normal node type.
- Optionalloy, set a reference for the field. This overrides the short reference set on the field and will be used to set the field reference in the database.
- End the reference with a # to denote that the field forms part of the logical unique key of the table.
You must have at least one key field, denoted with a #.
In the advanced section, set the short reference for the row. This will be used as the table name within your database.
The row type is a conventional node type, and you can create instances of your node anywhere.
With the exception of the boolean type (which is implemented as a link), do not use link fields on the row. To represent links between tables, use Parent Field on the child row to hold a link to the parent row. For example, if you have an order table and an order line table, you would have a parent field on order line which links back to the parent.
You can set a cascade delete option which deletes children rows when the parent is deleted.
Children fields are the opposite of parent fields. They do not hold data, but they are used to navigate from the parent to the child. They can also be used to set children along with the parent. Children fields are optional, but make it easier to find all the children for a parent.
Use the Children Field on the parent row to represent the children. You will need to identify the child table (by reference) and the field on the child table that links to the parent.
You can select whether the children can be updated with the parent. Only set this where the children are fundamentally owned by the parent (such as order lines and orders). In these cases, the parent field link on the child would usually be part of the key of the child row, and the parent link field on the child would usually have the cascade delete option set.
In a local databases, files are represented by a link field which links to a file node. The file node itself is held within the row. The scripting and web service APIs provide comprehensive support for setting and accessing the files.
Use the File Field to represent a file link.
If you need to be able to upload files directly using the row API (i.e. not through a program that accesses the database), use the standard Upload Field type to create an upload field, and identify the file field as the attachment.
Local databases work best with simple fields in a normalised model. If you have fields with multiple parts (i.e. more than one of text, number and link), repeating fields, or link fields that are not links to other rows in the same databases, the local database components will deal with this as an "entity field". This is an experimental feature and is not recommended for production use.
Creating the database
To create a database:
- Create in your library area an instance of Database Prototype. On this, use the Row types field to link to each of the row types you want in the database.
- Create an instance of Database in your data area, and use inheritance to inherit from your database prototype. The database will automatically create a folder for each table listed in the prototype.