Accessing Databases Using JDBC - Java Programming: Comprehensive Concepts and Techniques - page 695

Designing a Relational Database
Before creating the Java program that initializes the database, it is necessary to
design the database in terms of the tables it will contain, as well as the content of
each table. As noted earlier, a relational database is comprised of data in tables,
grouped in rows and columns. A column represents an individual data element,
or field, such as a user ID, first name, or last name. All of the columns represent
the logical contents of the table. The rows contain the actual data values for a
given record, such as a particular user ID or first name.
In this application, a table is needed to hold information about a user,
specifically the user ID, first name, last name, password (an object), and an indi-
cation of whether the user is an administrative user. Table 11-2 lists the tables
and fields used in the StockTracker database. Because an Access database will be
used, the Access data type and size, where appropriate, are listed with each field.
Text fields should be large enough to accommodate the largest possible, or
allowed, value to be stored. Other fields, such as fields for binary objects, do not
require a specified size, because the size is based on the type of data.

Table 11-2 StockTracker Database Tables and Fields

TABLE NAME FIELD NAME DATA TYPE FIELD SIZE

Users userID Text 20

lastName Text 30

firstName Text 30

pswd Long Binary n/a

admin Boolean n/a

Stocks symbol Text 8

name Text 50

UserStocks userID Text 20

symbol Text 8

When designing tables for a database, you must consider what data you want
to store and how that data relates to a given record. For a given row and column,
there exists only a single value, never a set of values. When the condition of
having no repeating groups is satisfied, the data is said to be normalized . More
specifically, such data is said to be in first normal form , because additional rules
of normalization can be applied to database design. Data that repeats for a record
typically becomes another table. For example, a user record for the stock-tracking
system has one user ID, one first name, and one last name, but may have zero,
one, or many stock holdings. If space is reserved for a fixed number of stock
holdings per user, when the user has no holdings, that space is wasted.
Further, the user could not track more stock holdings than the maximum the
record accommodates. A more flexible solution is to put this data in another
table and then establish a relationship , which is an association between fields