Relational Database Fundamentals - Design and Use of Relational Databases in Chemistry - page 8

performed on the data in these columns. While it may be more computa-
tionally efficient to use the float data type and it may take less space in the
database, the numeric data type is recommended for most scientific data,
unless rounding errors are of no importance. There is also a double data
type that lessens any rounding errors, but it does not prevent them.
Sometimes, there is no value available for a particular column or a par-
ticular row. Rather than inventing a special value to represent this, such as
99999 for numeric or ““ for text, the relational database provides a special
null value. This should be used when a value is unknown or unavailable.
When actual data becomes available, the null value can be updated.
2.4 Advantages over Spreadsheets
The advantages of using data types discussed above apply to all relational
tables regardless of size or complexity, but they are missing from spread-
sheet programs, or are difficult to implement. Spreadsheet programs allow
the data in a column to be formatted according to rules, such as for dates
or numbers. However, these rules are simply formatting rules and are not
enforced for all data input into a particular column. This reflects that the
emphasis in typical spreadsheet programs on display rather than data.
There are other valuable checks (see Constraints below) on data correct-
ness that are easily implemented in relational tables but are impossible or
clumsy when using spreadsheets.
2.4.1 Size and Speed
Table 2.1 could be easily and efficiently stored as a spreadsheet file. If the table
grows to millions of rows, the ability of spreadsheet programs to update,
sort, and otherwise manipulate the data would be severely impacted, or
even impossible. A relational database has no inherent limit on the number
of rows a table may contain because its rows are independent of one another.
Even in a relational database, selecting or searching a table containing mil-
lions of rows takes longer than searching a table containing hundreds of
rows, but the operation scales predictably. For example, if every row must be
searched to select the ones desired, the operation will scale linearly.
In a relational database, data in a column may be indexed. This is
explained in a later section of this chapter. If the relational table contains
an index, many operations on the table can be greatly accelerated. Indexing
is not possible in most spreadsheet programs.
2.4.2 Multiple Users
Only one person at a time can effectively use a spreadsheet, especially
if it is being updated. Relational databases are designed to be used by