Database Tables

Overview

The App Database stores data in Tables. A Database Table is like a spreadsheet. It's a collection of data about a specific topic, such as Customers, Products, etc.


Table Fields

A Database Table is made up of smaller categories of data called fields (also known as a Column or Attribute).

e.g. A Database Table about customers might have fields such as: CustomerID, First Name, Last Name, Phone Number, Email Address and Notes.

Field Types

Fields are defined by specifying it's name and the type of data you want it to contain. The table below lists the supported field types:

TypeSub-TypeDescription

Text

Allow any text

Simple text

Multi-line

Simple text spanning multiple lines

Multi-line HTML

Formatted text spanning multiple lines

Email

Email addresses

Phone

Phone Numbers

Password

Passwords with encryption

List of Values

Allows only a Pre-defined set of simple text

Files

Used for uploading and associating files. This field will store information about the files.

Numeric

-

Integer numbers within the approx. range -2 million to +2 million (32-bit signed)

Numeric (decimals)

-

Real numbers within the range (64-bit double precision)

Date/Time

-

A timestamp containing the date and time.

Yes/No

-

Boolean values like true or false, yes or no, on or off, etc

Large Numeric

-

Integer numbers (64-bit signed)

Unique Fields

A Database Table can have a single or a group of fields marked as unique.

In the case of a single field being marked as unique, no two rows can have the same value for this field.

In case of a group of fields, the combination of values across these fields must be unique for each record in the table.


ID Field

Every Database Table in Zingy gets an automatically assigned ID field. The value of this ID field is automatically generated and is guaranteed to be unique across all the records in the specific table.


Table Records

Database Tables store data in the form of records. A record (also called a Row or Tuple) is an individual entry in the table.

For example, in the Database Table about customers, each record would contain specific information about one customer which is the data of the fields: CustomerID, First Name, Last Name, Phone Number, Email Address and Notes .

The above image shows a record selected in the Customers Database Table. The record is for the customer named Max Mustermann.


Creating a Database Table

In the App Editor, go to the Data tab and use the āž•New Table icon to add a new Database Table to your App.

In the screen that follows, you simply provide the name of the table (unique to your app) and add one or more fields by using the Add Field button.

The image below shows the information required for adding a field to the table.

Field Title

The general name for the field e.g. Address Line 1.

Type and Sub-Type

The type of data for the field. See here for more details.

Field ID

The Field ID gets automatically derived from the Field Title and is used internally to refer to the field.

Unique

Enable if no two rows can have the same value for this field. See here for more details.

Description

Optional text describing the field.

Values

For the List of Values sub-type for Text type fields, this contains a comma-separated text for the allowed values.

The the fields you want have been added click the Save Table button to create the table.


Modifying a Table

Altering the structure of a table is irreversible and can be time-consuming, especially if the table already contains data.

We advise against making such changes unless absolutely necessary.

If you need to modify a table's structure e.g. to add new fields or remove fields, click on the table and select the Edit option from the popup menu.

The image below shows an annotated example of the screen that shows for editing a Database Table.

The table below explains the annotations in the image above.

Edit a field's title, type, sub-type, etc. If the table contains data, any field type changes will result in the existing data being converted to the new field type. This maybe a lengthy process depending on the amount of records in the table.

Delete a field from the table. If the table contains data, this will result in the field being removed from all exiting records.This maybe a lengthy process depending on the amount of records in the table.

Add a new field to the table. If the table contains data, this will result in the new field being added to all existing records and initialized with a default value depending on the type of the field. This maybe a lengthy process depending on the amount of records in the table.

Save the changes and start the conversion process.


Deleting a Table

Database Table delete is permanent and not reversible.

To delete a Database Table and it's data, click on the table and select the Delete option from the popup menu.


Access (Security) Rules

To configure role based access for a Database Table, click on the table and select the Access (Security) option from the popup menu.

Access rules can be configured for 4 types of operations (View, Add, Modify and Delete) as shown below:

OperationDescription

View

Listing records, looking up records, etc

Add

Creating or adding new records

Modify

Editing records (NOTE: this also requires View permission)

Delete

Deleting records

By default all registered Users of the app and members of the Admin group are allowed on the four operations (View/Add/Modify/Delete). You can change this by clicking on any of the Edit buttons highlighted in the image above.

  • To allow a group, select it (1), choose the minimum role level (2) and click on the Add button (3).

  • To grant public access to an operation, select the "Public" group.

  • To remove a group click on the 'x' icon (4). Note: The "Admin" group cannot be removed.

Last updated