Creating a database and its tables
Under Construction
Overview
Data is held in tables. Tables consist of columns and rows, also referred to as fields and records. 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.
Tables are grouped into a database.
Southwind 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.
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 91 rows - each row refers to one customer.
(pic) 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.
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.
(pic) 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.
(pic) The employees table. (pic) The shippers 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 it to its order in the orders table.
(pic) The order_details table.
It includes a Product ID (productid) foreign key to reference the primary key in the products table.
(pic) The products table.
It does not need the Customer ID as that information is held in the orders table.
Here are the customers, order and order_details tables related using the Relationship Builder.
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.
Data Workspace
See Data Workspace.
Databases and tables can also be created using SQL
