Databases Reference
In-Depth Information
document. If your system has many places that require careful transaction control,
RDBMSs may be the best solution.
3.4.2
Fixed data definition language and typed columns
RDBMS
s require you to declare the structure of all tables prior to adding data to any
table. These declarations are created using a
SQL
data definition language (
DDL
),
which allows the database designer to specify all columns of a table, the column type,
and any indexes associated with the table. A list of typical
SQL
data types from a
MySQL system can be seen in table 3.2.
Table 3.2 Sample of RDBMS column types for MySQL. Each column in an RDBMS is assigned
one type. Trying to add data that doesn't contain the correct data type will result in an error.
Category
Types
Integer
INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
Numeric
DECIMAL, NUMERIC, FLOAT, DOUBLE
Boolean
BIT
Date and time
DATE, DATETIME, TIMESTAMP
Te x t
CHAR, VARCHAR, BLOB, TEXT
Sets
ENUM, SET
Binary
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
The strength of this system is that it enforces the rules about your data up front and
prevents you from adding any data that doesn't conform to the rules. The disadvan-
tage is that in situations where the data may need to vary, you can't simply insert it into
the database. These variations must be stored in other columns with other data types
or the column type needs to be changed to be more flexible.
In organizations that have existing databases with millions of rows of data in tables,
these tables must be removed and restored if there are changes to data types. This can
result in downtime and loss of productivity to your staff, your customers, and ulti-
mately the company bottom line. Application developers sometimes use the metadata
associated with a column type to create rules to map the columns into object data
types. This means that the object-relational mapping software must also be updated at
the same time the database changes.
Though they may seem like minor annoyances to someone building a new system
with a small test data set, the process of restructuring the database in a production
environment may take weeks, months, or longer. There's anecdotal evidence of orga-
nizations that have spent millions of dollars to simply change the number of digits in a
data field. The Year 2000 problem (
Y2K
) is one example of this type of challenge.
