Designing databases
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.
- 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.
- 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.
- 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® documentation, Db2 best practices, and in the many Db2 retail books that are available on this subject.