Creating a database and its tables
Under Construction
Contents
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.
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.
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.
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.
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.
Shippers Table
The shippers table holds information about the shipping company who shipped the order.
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.
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.
Related Tables
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.
Before creating your database and tables, do some planning.
- Database: one or more databases?
Lianja opens one database at a time (Note1) 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. 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.
SQL
Databases and tables can also be created using SQL
Note1: You can have Virtual Tables that connect to other Lianja or third-party databases, but that's not for this tutorial.
