Oracle7 Server Application Developer's Guide
Designing the Database
At this point, you are ready to begin designing your data model. This model will allow you to determine how your data can be most efficiently stored and used. The Entity-Relationship model is often used to map a real-world system to a relational database management system.
The Entity-Relationship model categorizes all elements of a system as either an entity (a person, place, or thing) or a relationship between entities. Both constructs are represented by the same structure, a table. For example, in an order entry system, parts are entities, as are orders. Both part and order information is represented in tables. The relationship of which parts are requested by which order is also represented by a third table. The application of the Entity-Relationship model requires the following steps.
- First, identify the entities of your system and construct a table to represent each entity.
- Second, identify the relationships between the entities and either extend the current tables or create new tables to represent these relationships.
- Third, identify attributes of each entity and extend the tables to include these attributes.
When modeling a system with the Entity-Relationship model, you will often include a step called normalization. Textbooks on database design will tell you how to achieve Third Normal Form. Each table must have exactly one primary key and, in third normal form, all of the data in a table is dependent solely upon the table's primary key. You might find it necessary to violate normal form on occasion to achieve a desired performance level.
Proper application of the Entity-Relationship model results in well designed tables. The benefits of a set of well designed tables include the following:
- reduced storage of redundant data, which eliminates the cost of updating duplicates and avoids the risk of inconsistent results based on duplicates
- increased ability to effectively enforce integrity constraints
- increased ability to adapt to the growth and change of the system
- increased productivity based on the inherent flexibility of well designed relational systems
Oracle Corporation's products for database design, CASE*Dictionary, CASE*Method, and CASE*Designer, can help improve, automate, and document designs. See the CASE manuals for additional information.
After determining the overall structure of the tables in your database, you must next design the structure of these tables. This process involves selecting the proper datatype for each column and assigning each column a meaningful name. You can find information about selecting the appropriate Oracle datatype in Chapter 5 of this Guide.
If you are creating an application that runs on a distributed database, you must also determine where to locate this data and any links that are necessary to access the data across the network; see Oracle7 Server Distributed Systems, Volume I for additional information.