Revision as of 19:04, 12 December 2017

Under Construction

Quick Start Guide

Select the "Data" workspace.
Select the "Database" Tab.

Overview

A database is a container holding a collection of tables, views, and their associated constraints and business rules. Tables consist of rows and columns, also referred to as records and fields. Each column or field has a name, a data type and a width. It can optionally have decimal places, a description and data validation rules. Data is added to the table by adding rows or records.

Southwind Sample Database

The Lianja App Builder distribution includes the sample southwind database and its tables. It is used in the demo and example Apps (also included in the distribution) and in many of the tutorials and documentation.

Let's have a look at some of the tables in the southwind database and how they can be related.

Customers Table

One of the key tables in the southwind is the customers table. It holds information about customers. Each column - the customers table has 12 columns - holds a specific piece of information about the customer: their name, city, phone number etc. It has multiple rows, each referring to an individual customer.

The customers table in the Data Workspace

As well as the contact and address information, the customers table has an additional field: Customer ID (customerid). Each customer has a different customerid, so this gives a unique reference to the customer and their record in the customers table. Here the customerid is an upper-case five letter abbreviation of the customer's name, but we will see other ways to generate a unique key to reference a row, known as the primary key.

Orders Table

The orders table holds information about orders made by a customer, for example the dates when the order was made and shipped and the address where it was shipped.

The orders table in the Data Workspace

Notice that the orders table includes a Customer ID (customerid) column. This is called a foreign key. It allows the record in the orders table to be related to the record in the customers table belonging to the customer who placed the order.

The orders table must also have its own primary key to uniquely identify each order. The orders table primary key is Order ID (orderid), a 10 digit numeric.

It also has two further foreign keys: Employee ID (employeeid), which references the primary key in the employees table; and Ship Via (shipvia), which references the primary key in the shippers table.

Employees Table

The employees table holds information about the employee who handled the order.

The employees table in the Data Workspace

Shippers Table

The shippers table holds information about the shipping company who shipped the order.

The shippers table in the Data Workspace

Order_details Table

The order_details table holds information about individual line items for a customer's order. It has an Orderid (orderid) foreign key to relate each order line to its order header in the orders table. It does not need the Customer ID as that information is held in the orders table.

The order_details table in the Data Workspace

It also includes a Product ID (productid) foreign key to reference the primary key in the products table.

Products Table

The products table holds information about individual products.

The products table in the Data Workspace

Related Tables

Here are the customers, order and order_details tables related using the Relationship Builder.

Related Tables

The top section is a Form Section bound to the customers table.

The middle section is a Grid Section bound to the orders table. The relation between the top section and this section based on the Customer ID means that only orders for the current customer are displayed in the orders grid.

The bottom section is a Grid Section bound to the order_details table. The relation between the middle section and this section based on the Order ID means that only order details for the current order are displayed in the order_details grid.

Points to Consider

Before creating your database and tables, do some planning.

• Database: one or more databases?

Lianja opens one database at a time (note) so make sure all the tables you need for your App are in a single database. But, if you are creating multiple Apps dealing with separate data, these can use separate databases.

• Which information is recurring and which is unique information?

As in the example from the southwind database, there is no need for each line item in the order_details table to store the Customer ID or the shipping information. This information is stored in the orders table. And the orders table does not need complete information about the employee who created the order or the shipping company that shipped it. All it needs is foreign key fields to reference the primary keys in the other tables.

• What should the name, data type and width be for each column?

Meaningful names make them easier to remember (and using tableid for primary and foreign keys is ideal when using the Relationship Builder). Data type: will a particular data type make life easier for your users? For example date columns have a popup calendar. Or easier for development - if you want to be able to carry out mathematical operations, use a numeric or int. Width: think ahead, will the reference number be big enough when you hit order 1 million, not just 1 thousand. And check that the widths and data types of corresponding primary and foreign keys match.

Data Workspace

The Data Workspace is where you perform database administration operations on your data. It also allows you to browse and modify your data and view it in a variety of different data views: Grid, Form and Split View.

Create a Database

To create a new database, either:

• Click the Create a Database tile in the Home Workspace. This will take you into the Data Workspace.

or

• Select the Data Workspace and click the New Database toolbutton in the Headerbar or the New icon at the bottom of the Database Names panel.

Data Workspace: New Database

You will be prompted to enter a name for the new database. Click OK to continue or Cancel to cancel.

Pro Tip

Your database name must be unique - you will be prevented from using the same name as an existing database.

After you click OK, your new database will be created and opened and the Names tab in the Tables tab will be automatically selected.

Create a Table

With your new database open, you can now start creating your tables.

Click the New icon at the bottom of the Table Names panel.

Tip

To open a database:
Select the Data Workspace.
Select the Database Tab.
Double click the database name.

After clicking New, the CREATE A TABLE dialog will be displayed with a range of table templates. The templates are arranged by Table Group, with each group appearing in a Table Category.

Create a Table: Template

Tip

You can create all the tables from a Table Group by selecting a category and a group and clicking Create Group.
Or all the tables from a Table Category by selecting a category and the All group and clicking Create Group.
Or even all the template tables by selecting the All category and the All group and clicking Create Group.

After clicking Create the dialog will close and the new table will be listed in the Table Names panel with its columns displayed in the Column Names panel and the attributes for the first column shown in the Column Attibutes panel.

Create a Table: Template

Tip

The structure of your template table can now be changed.
You can add new columns or change or delete the pre-built columns: see Create and Modify Columns below.

You do not need to use a table template. If you select Blank in the CREATE A TABLE dialog, you can create a table with an empty structure ready for you to define.

Create a Table: Blank

After clicking Create the dialog will close and the CREATE A COLUMN dialog will be opened for your new table.

Create a Table: Blank

Next we will see how to create and modify columns.

Create and Modify Columns

The slide-in CREATE A COLUMN / MODIFY A COLUMN dialog is where you define your table's columns. As mentioned above, each column has a name, a data type and a width and can optionally have decimal places, a description and data validation rules. These are all defined in the dialog (note).

New

To add a new column, click the New (+) icon at the bottom of the Column Names panel.

Modify

To modify a column, double-click the column name in the Column Names panel. Alternatively, select the column name in the panel then click the Additional commands (cog) icon at the bottom of the panel and select Modify.

Delete

To delete a column, select the column name in the Column Names panel, then click the Delete (trash can) icon at the bottom of the panel.

See Also

Databases and tables can also be created using SQL in the console workspace, command window or in a program.

• CREATE DATABASE
• CREATE TABLE
• ALTER TABLE
• OPEN DATABASE

If you have existing VFP Data you can import it directly into Lianja format.

• Importing Visual FoxPro Databases

Notes for Advanced Users

For more advanced users, the following commands, functions and commands are also available:

Note1

You can have Virtual Tables that connect to other Lianja or third-party databases.

Note2

Columns can also be altered using the SQL alter table command from Lianja/VFP scripts or the Console Workspace / Command Window.