SQL, or Structured Query Language, is a programming language designed to help you manage data held in a relational database management system (RDBMS). SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Since then, the standard has been revised to include a larger set of features. Despite the existence of such standards, though, most SQL code is not completely portable among different database systems.
QuickEasy uses the Firebird relational database, so naturally this guide will adhere to the standards that work in Firebird and will focus on Data Manipulation Language (DML), the scope of the SQL language which is used to insert, update, delete and query a database.
FlameRobin is a free Data Administration tool that can be used to access the Firebird database directly using SQL.
How tables are linked
A Database is a collection of tables and each table contains specific Data. eg. you might have a table for Customers, Suppliers, Transaction Headers, Items, etc.
The Tables in the database are linked using Primary Keys and Foreign Keys. Most of the time a one-to-many relationship will exist between linked tables. This is also referred to as a Master-Detail relationship or a Parent-Child relationship. In this case the Foreign Key field in the child/detail table will link to the Primary Key field in the Master/Parent table. Below is an explanation of how Primary Keys and Foreign Keys work.
- Primary Keys - Every table must have at least one field that uniquely identifies each record in the table. This is called the Primary Key (PK) and in QuickEasy an integer field is used to serve as the Primary Key in each table.
- Foreign Keys - Foreign Keys are used in the Child/Detail tables to link to the Primary Key in the Parent/Master tables and they need to be of the same data type as the Primary Keys to which they are linked.
Primary Key / Foreign Key relationships are only used where a relationship is mandatory. For instance, a Transaction Item can only exist if it is linked to a Transaction Header and if the Transaction Header is deleted, the Items on the Transaction must also be deleted.
Different Data Types are used to store different types of information, eg, Text, Numbers, Dates, Images, etc. Each field in a table is therefore linked to a Data Type. Following is an overview of the data types that are most commonly used in QuickEasy.
Text Data Types
- VARCHAR(XX) - Used to store text, the 'xx' determines the maximum number of characters that may be stored in the field. Typically used for Name and Description fields
- BLOB SUB_TYPE TEXT - Used to store text, but there is no limit to how many characters may be stored in the field - typically used for 'Note' fields.
Numerical Data Types
- INTEGER - Used to store numeric values that don't have decimal values. In QuickEasy they are mostly used for Primary and Foreign Keys, Settings and True/False fields.
- FLOAT - Used to store numerical values with decimals. Also called a floating point data type. Typically used to store quantities and other numerical values that might be used in calculations, eg. an Estimate quantity or a Stock quantity.
- NUMERIC(18,X) - Used to store currency values. The 'X' determines the number of decimals, usually 2 or 4.
Date and Time Data Types
- DATE - Only stores a date. Used for transaction dates.
- TIME - Only stores a time. This would typically be used in settings, eg. The Start and End times of a working day or shift.
- TIMESTAMP - Stores a date and a time value. Typically used for due dates, time keeping, audits, etc. where the time of the day is as important as the day itself.
Images and Files
- BLOB SUB_TYPE 0 - This datatype can typically store any file that you can save on your hard-drive, including image files.