The first step in developing a database design is to identify the types of data to be stored in database tables. A database includes information about the entities in an organization or business and their relationships to each other. In a relational database, entities are defined as tables.
An entity is a person, object, or concept about which you wish to store information. Some of the entities described in the sample tables are employees, departments, and projects. (Refer to the "Sample Tables" in the Administration Guide, Performance, for a description of the sample database.)
In the sample employee table, the entity "employee" has attributes, or properties, such as employee number, name, work department, and salary amount. Those properties appear as the columns EMPNO, FIRSTNME, LASTNAME, WORKDEPT, and SALARY.
An occurrence of the entity "employee" consists of the values in all of the columns for one employee. Each employee has a unique employee number (EMPNO) that can be used to identify an occurrence of the entity "employee".
Each row in a table represents an occurrence of an entity or
relationship. For example, in the following table the values in the
first row describe an employee named Haas.
Table 2. Occurrences of Employee Entities and their Attributes
| EMPNO | FIRSTNME | LASTNAME | WORKDEPT | JOB |
|---|---|---|---|---|
| 000010 | Christine | Haas | A00 | President |
| 000020 | Michael | Thompson | B01 | Manager |
| 000120 | Sean | O'Connell | A00 | Clerk |
| 000130 | Dolores | Quintana | C01 | Analyst |
| 000030 | Sally | Kwan | C01 | Manager |
| 000140 | Heather | Nicholls | C01 | Analyst |
| 000170 | Masatoshi | Yoshimura | D11 | Designer |
There is a growing need to support non-traditional database applications such as multimedia. Within your design, you may want to consider attributes to support multimedia objects such as documents, video or mixed media, image, and voice.
In a table, each column of a row is related in some way to all the other columns of that row. Some of the relationships expressed in the sample tables are:
Before you design your tables, you must understand entities and their relationships. "Employee" and "department" are entities; Sally Kwan is part of an occurrence of "employee," and C01 is part of an occurrence of "department".
The same relationship applies to the same columns in every row of a table. For example, one row of a table expresses the relationship that Sally Kwan manages Department C01; another, the relationship that Sean O'Connell is a clerk in Department A00.
The information contained within a table depends on the relationships to be expressed, the amount of flexibility needed, and the data retrieval speed desired.
In addition to identifying data within your design, you should also identify other types of information such as the business rules which apply to that data.