Defining a Schema

A poorly designed database can create all kinds of difficulties for the user of a database application. Unfortunately, the blame for those difficulties is often laid at the feet of the database management system which, try as it might, simply cannot use non-existent access paths to quickly get at the needed data. Good database design is as much of an art as it is engineering and a solid understanding of the application requirements is a necessary prerequisite. However, it is not the purpose of this document to teach you how to produce good database designs. But you do need to understand that designing a database is a complex task and that the quality of the application in which it is to be used is highly dependent on the quality of the database design. If you are not experienced in designing databases then it is highly recommended that you first consult any number of good books on that subject before setting out to develop your RDM database.

Information in the RDM database is stored in tables. Each table is composed of columns that store a particular type of information and rows that correspond to a particular record in the table. A simple but effective analogy can be made with a file cabinet as illustrated in Figure 6-1.

Figure 6-1. A File Cabinet is a Database

A file cabinet contains drawers. Each drawer contains a set of files organized around a common theme. For example, one drawer might contain customer information while another drawer might contain vendor information. Each drawer holds individual file folders for each customer or vendor, sorted in customer or vendor name order. Each customer file contains specific information about the customer. The cabinet corresponds to a database, each drawer is like a table, and each folder is like a row in the table.

Typically, tables are viewed as shown in Figure 6-2, where the basic components of a database table are identified in an example customer table. Each column of the table has a name that identifies the kind of information it contains. Each row gives all of the information relating to a particular customer.

Figure 6-2. Definition of a "Table"

Suppose that you want to expand this example further and define a simple sales order database that, initially, keeps track of salespersons and their customers. Figure 6-3 shows how this information could be stored in the table.

Figure 6-3. Salesperson Accounts Table

There are columns for each salesperson's name and commission rate. Each salesperson has one or more customer accounts. The customer's company name, city, and state are also stored with the data of the salesperson who services that customer's account. Note that the salesperson's name and commission are replicated in all of the rows that identify the salesperson's customers. Such duplicated data is called redundant data. One of the goals in designing a database is to minimize the amount of redundant data that must be stored in a database.

A database schema is the definition of what kind of data is to be stored and how that data is to be organized in the database. The Database Definition Language (DDL) consists of the SQL statements that are used to describe a particular database schema (also called the database definition).

A database schema is the definition of what kind of data is to be stored and how that data is to be organized in the database. RDM databases are defined using the Database Definition Language (DDL) specified by the ISO/ANSI SQL Standard. Even if you intend to only use the RDM native (non-SQL) API to access and manipulate your database data, you still need to use the RDM DDL that is based on the SQL standard to define your database schema.

The database schema definition will consist of at least one or more CREATE TABLE statements. Detailed descriptions can be found by clicking on the DDL statement name.

CREATE TABLE	Create a new table
CREATE CIRCULAR TABLE	Create a circular table (limited # of rows)