Database Terminology
Database: A database is a collection of related data that can be persisted from an outside
source. The database can be very complex, holding millions of rows of data, or as simple as just a few users in a secure data set.
Table: Tables are made up of columns and rows, similar to a spreadsheet. The columns are
given unique names to define data elements.
Keys: Keys are unique indicators that point to a row or rows of data. A primary key is a
unique identifier that can identify a single row in a table. A foreign key is a key in a table
that can identify a row in another table. The primary-to-foreign key connection is the
foundation of a relational database.
Database Design Tips
• Remove redundant data in tables. Break out common objects like addresses and phone
numbers.
• Columns should not represent data sets like invoice1, invoice2, and so on. That is the
purpose of rows.
• Be careful about using the AUTO_INCREMENT attribute for a table. In our example, we will need to create a unique generation scheme for objectIdentifier values, so we always
have unique numbers for each table’s primary keys.
• Keep the storage requirements as small as possible to reduce disk space consumption.
• Identify frequent queries to properly tune the database.
• Keep naming conventions in mind when creating your tables. (Table names are casesensitive; column names are not.)
• When you first install the database, you will have an initial user of root. Set the password, and then rename the user.
• Let Tomcat or your application server control database pooling where appropriate.
• Lock down the database install directory by allowing access only by a special user on
the operating system. That way, your data files are protected from an administrator or
another user accidentally corrupting them.
• Keep media objects like files and movies out of the database. It is better to just store
pointers to the media located on the file system.
MySQL Database Overview
Types of Tables in MySQL
For building RIAs, three table types are of interest:
HEAP: HEAP tables are used when a temporary table is created in MySQL.
MyISAM: MyISAM tables are most suitable when disk space and read efficiency are high
priorities.
InnoDB: InnoDB tables offer database transactions, row-level locking, foreign key constraints, and crash recovery. If you have an application that requires transactions, greater database security, or many concurrent users making changes, consider setting your tables to the InnoDB type.
Data Types in MySQL
Type Data Type Description
CHAR() Text Fixed character set, 0–255
VARCHAR() Text Variable character set, 0–255
TINYTEXT Text String maximum of 255 characters
TEXT Text String maximum of 65,535 characters
BLOB Text String maximum of 65,535 characters
MEDIUMTEXT Text String maximum of 16,777,215 characters
MEDIUMBLOB Text String maximum of 16,777,215 characters
LONGTEXT Text String maximum of 4,294,967,295 characters
LONGBLOB Text String maximum of 4,294,967,295 characters
TINYINT() Number –128 to 127 normal 0 to 255 unsigned
SMALLINT() Number –32,768 to 32,767 normal 0 to 65,535 unsigned
MEDIUMINT() Number –8,388,608 to 8,388,607 normal 0 to 16,777,215 unsigned
INT() Number –2,147,483,648 to 2,147,483,647 normal 0 to 4,294,967,295 unsigned
BIGINT() Number –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 normal
0 to 18,446,744,073,709,551,615 unsigned
FLOAT Number Small number with decimal point
DOUBLE(,) Number Large number with decimal point
DECIMAL(,) Number DOUBLE stored in a string with a fixed decimal point
DATE Date Formatted date, YYYY-MM-DD
DATETIME Date Formatted date and time, YYYY-MM-DD HH:MM:SS
TIMESTAMP Date Formatted date and time, YYYYMMDDHHMMSS
TIME Date Formatted time, HH:MM:SS
ENUM() Misc Enumerated column with defined values
SET Misc Each column can have more than one defined value
AF – Client Manager Data Model
It is a relational model that uses objectIdentifier-to-assocobjectID links to associate data with the proper owning object. Every table will have an objectIdentifier as the primary key to the object. For all deletions and updates, the objectIdentifier will be used to manipulate the associated data.
AF – Client Manager Database Table Creation
Using the Command-Line Interface
MySQL supports basic command-line interfaces for SQL editing. You can open your favorite
command-line interface, such as Cygwin or Windows cmd.exe. You can add MySQL to your
system PATH by setting the install location’s bin directory in your PATH, as follows:
PATH = C:\Program Files\MySQL\MySQL Server 5.0\bin
This will allow you to open your command-line editor and use the MySQL binary to execute
database commands.
To log in to your MySQL database, execute the following command:
mysql –u username -p
Creating the AF – Client Manager Tables
The AF – Client Manager has many objects that we need to place in persistent storage to support the Flex data visualization in the user interface.
The afcm_client Table
CREATE TABLE `apress`.`afcm_client` (
`objectIdentifier` BIGINT NOT NULL,
`assocobjectID` BIGINT NOT NULL,
`clientName` VARCHAR(120),
`clientID` INTEGER UNSIGNED,
`link` VARCHAR(120),
`image` VARCHAR(45),
`description` TEXT,
`notes` TEXT,
`phone` VARCHAR(45),
`addressLine1` VARCHAR(120),
`addressLine2` VARCHAR(120),
`city` VARCHAR(120),
`state` VARCHAR(20),
`zip` VARCHAR(10),
PRIMARY KEY(`objectIdentifier`)
)
ENGINE = InnoDB;
The afcm_invoiceDetails Table
CREATE TABLE `apress`.`afcm_invoiceDetails` (
`objectIdentifier` BIGINT NOT NULL,
`assocobjectID` BIGINT NOT NULL,
`detail` TEXT,
`quantity` TINYINT,
`unitPrice` FLOAT,
PRIMARY KEY(`objectIdentifier`)
)
ENGINE = InnoDB;
