ASA Getting Started
Designing and Building Your Database
Tutorial: Design and build a simple database
In this lesson, you learn more about choosing data types and other attributes for the columns of your tables.
Each column has a data type associated with it. The data type defines the type of information the column holds. Choose a data type for the column that is appropriate for the data in the column. For example, identifier columns commonly have an integer data type, while columns holding names or addresses must have character data types.
Data types are organized into the following categories:
Numeric data types There are several numeric data types. Some are exact (not affected by round-off errors during operations) and some are approximate.
The data type of the column affects the maximum size of the column. For example, if you specify SMALLINT, a column can contain a maximum value of 32,767. If you specify INTEGER, the maximum value is 2,147,483,647.
For a complete list, see Numeric data types.
Character data types These are used to hold strings of text, such as names, addresses, and so on. These data types have a length indicating the maximum length of string that can be stored in them.
For a list, see Character data types.
Binary data types These can be useful to hold information that may be meaningful to an application, but is encoded in a binary format.
For a list, see Binary data types.
Date/time data types These hold times of the day, as well as dates.
For a list, see Date and time data types.
Long data types These are sometimes called blobs (binary large objects). They can be used to hold long strings of text (called memo fields in some databases), images, or other binary information.
For more information, see LONG BINARY data type [BINARY], and LONG VARCHAR data type [Character].
In addition, Adaptive Server Anywhere supports user-defined data types and special Java data types. These are not discussed in this introductory book.
If every row must contain a value for this column, you should define the column as being NOT NULL. Otherwise, the column is allowed to contain NULL, which represents a missing value. The default is to allow NULL, but you should explicitly declare columns NOT NULL unless there is a good reason to allow NULL.
For a complete description of the NULL value, see NULL value. For information on its use in comparisons, see Search conditions.
To specify a data type for a column
Select the product table in the left pane, then click the Columns tab in the right pane.
Select the primary key column and then choose File
The column's property sheet opens.
Click the Data Type tab.
Select a data type from the Built-in Type dropdown list.
This lesson and the last lesson have introduced the basic concepts you need to know in order to create database tables. You can put these to work by adding some more tables to your database. These tables will be used in the subsequent lessons in this chapter.
Add the following tables to your database:
customer Add a table named customer, with the following columns:
id An identification number for each customer. This column has integer data type, and is the primary key. Make this an autoincrement key.
company_name The company name. This column is a character data type, with a maximum length of 35 characters.
sales_order Add a table named sales_order, with the following columns:
id An identification number for each sales order. This column has integer data type, and is the primary key. Make this an autoincrement key.
order_date The date on which the order was placed. This column has date data type.
cust_id The identification number of the customer who placed the sales order. This column has integer data type.
sales_order_items Add a table named sales_order_items to hold line item information, with the following columns:
id The identification number of the sales order of which the line item is a part. This column has integer data type, and should be identified as a primary key column.
line_id An identification number for each sales order. This column has integer data type, and should be identified as a primary key column.
prod_id The identification number for the product being ordered. This column has integer data type.
You have now created four tables in your database. The tables are not yet related in any way. In the next lesson, you define foreign keys to relate the tables to one another.