Overview of Database Design Concepts
Following is a brief summary of some of the concepts
on database design that we are working on in class:
Remember these distinctions:
- a database consists of one or more tables
- a table contains records
- each record contains a specific number and type of data fields; all
of the records in a given table use the same layout
- a field contains a specific entity of data
- fields are strongly typed; every field uses one and only one data
type (e.g. character, numeric, boolean, etc)
Concepts of Relational Database Design
- Normalization: This is a model to avoid
redundancy ... don't put the same information in more than one place!
- Keys: Use keys to index your tables
and to reference information in other tables. A foreign
key references data in another table. A primary
key requires unique data in that specific field and is sorted in the current
table; it can be used as a foreign key reference
from another table. A composite key consists
of more than one field. A surrogate key consists
of a system-generated unique ID (number) to use as a primary key, generally
in place of a complex composite key.
- Data relationships: Data file relationships
are generally recognized as "1:1"; "1:many"; or "many:many".
This is the topic of keeping the data accurate and in good order. Data integrity
is enforced by applying a number of rules and concepts to your database:
- Data validation: When data are entered,
either by hand or by a file import, specific values of the data field must
be checked. For example, if it is a date, the information must represent a
valid date (e.g. 05/41/99 is not a valid date). Text can be spell-checked.
- Business rules are used to define data
validation for specific scenarios: e.g. a hotel might have only rooms 001
- 599 and so a room number "735" would be invalid. Business rules
are used to define the start and end period for acceptable dates (e.g. when
reservations are accepted); the ranges for many fields of data entry; and
specific calculations to verify information (such as the famous algorithm
used to verify whether a social security number is valid, etc).
- Data reconciliation: It is often necessary
to check data against a separate table. For example, in a database of paintings,
the name of the artist should be either culled from or checked against a table
of artists by use of a foreign key. (If the artist is not on the table,
the user should be prompted to add the artist to the table or to select another
artist, if that user has the appropriate permissions to do so.) One common
method to represent this information in the graphical user interface is via
drop-down boxes with a list of acceptable values.
- Avoid Data duplication: Do not permit
duplicate data records! The issue of duplicate data fields in the database
design is one of normalization; but permitting an identical record to be entered
(i.e. two records with the identical primary key) causes problems. If you
find that you are permitting duplicate data records, then you should probably
double-check your database design and fix it!
- Required fields: Some data fields must
be filled in for the table to function properly (e.g. key fields). Some fields
are required because of the functional specifications, e.g. in a hotel reservation
system, the hotel room number should always be a required field (i.e. the
user should not be allowed to leave it blank.)