When designing a database, you are modeling a real business
system that contains a set of entities and their characteristics,
or attributes, and the rules or relationships between those entities.
The first step is to describe the system that you want to represent.
For example, if you were creating a database for publishing system,
the system would contain several types of entities, such as books,
authors, editors, and publishers.
For
each of these entities, there are certain pieces of information, or
attributes, that you must record:- Books: titles, ISBN, date published, location, publisher,
....
- Authors: name, address, phone and fax numbers, email address,
....
- Editors: name, address, phone and fax numbers, email address,
....
- Publishers: name, address, phone and fax numbers, email
address, ....
The database needs to represent not only these types of entities
and their attributes, but you also a way to relate these entities
to each other. For example, you need to represent the relationship
between books and their authors, the relationship between books/authors
and editors, and the relationship between books/authors and publishers.
There are three types of relationships
between the entities in a database:
- One-to-one relationships
- In this type of relationship, each instance of an entity relates
to only one instance of another entity. Currently, no one-to-one relationships
exist in the scenario described previously.
- One-to-many relationships
- In this type of relationship, each instance of an entity relates
to one or more instances of another entity. For example, an author
could have written multiple books, but certain books have only one
author. This is the most common type of relationship modeled in relational
databases.
- Many-to-many relationships
- In this type of relationship, many instances of a given entity
relate to one or more instances of another entity. For example, co-authors
could write a number of books.
Because databases consist of tables, you must construct a set of
tables that will best hold this data, with each cell in the table
holding a single view. There are many possible ways to perform this
task. As the database designer, your job is to come up with the best
set of tables possible.
For example, you could create a single table, with many rows and
columns, to hold all of the information. However, using this method,
some information would be repeated. Secondly, data entry and data
maintenance would be time-consuming and error prone. In contrast to
this single-table design, a relational database allows you
to have multiple simple tables, reducing redundancy and avoiding the
difficulties posed by a large and unmanageable table. In a relational
database, tables should contain information about a single type of
entity.
Also, the integrity of the data in a relational database must be
maintained as multiple users access and change the data. Whenever
data is shared, ensure the accuracy of the values within database
tables in any of the following ways:
- Use isolation levels to determine how data is locked or isolated
from other processes while the data is being accessed.
- Protect data and define relationships between data by defining
constraints to enforce business rules.
- Create triggers that can do complex, cross-table data validation.
- Implement a recovery strategy to protect data so that it can be
restore to a consistent state.
Database design is a more complex task than is indicated here,
and there are many items that you must consider, such as space requirements,
keys, indexes, constraints, security and authorization, and so forth.
You can find some of this information in the DB2® Information Center, DB2 best practices, and in the many DB2 retail books that are available on this subject.