Database Tables
Last updated
Last updated
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.
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.
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:
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.
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.
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.
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.
The the fields you want have been added click the Save Table button to create the 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.
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.
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:
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.
| Type | Sub-Type | Description |
|---|---|---|
In the App Editor, go to the Data tab and use the New Table icon to add a new Database Table to your App.
| Operation | Description |
|---|---|
Text
Allow any text
Simple text
Multi-line
Simple text spanning multiple lines
Multi-line HTML
Formatted text spanning multiple lines
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)
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.
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
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.
