0638-0641

Previous Table of Contents Next

Page 638

column constraints is revisited briefly later in this chapter, and all options will be discussed in full detail in the chapter on enforcing database integrity.

The primary key constraint is an important consideration for a number of reasons. At this point, it is important to recognize that Oracle will automatically generate a unique index for this column. This should not be overlooked in capacity planning.

The tablespace Contact_Main does not exist yet, but scripts referencing the tablespace help determine its size. The STORAGE specification indicates that 1MB will be allocated initially, that additional extents will start at 100KB, that there will be a minimum of 1 extent and a maximum of 10 extents, and that each extent will be 10 percent larger than the previous extent. Obviously, this specification will allow the table to grow to well over 2MB, which is the planned maximum. Although storage specifications can be modified using ALTER TABLE in the additional design phase and for capacity planning purposes, it is usually best to allow for the maximum estimated size or more. This is based on the assumption that it is better to overestimate storage requirements than to underestimate them.

After DDL scripts have been created for each of the tables, scripts can be written for the tablespaces that will house them. Assume that the sample application will store all application tables in the tablespace named Contact_Main. The data file(s) created with the tablespace should be large enough to contain all tables at their full size (which can be calculated based on the INITIAL, NEXT, and PCTINCREASE parameters in the STORAGE clause of each table). The script to create Contact_Main might look like the one in Listing 25.2.

Listing 25.2. DDL script that creates a tablespace in a single data file.

 CREATE TABLESPACE Contact_Main     DATAFILE `/oradata/tables/main1.dat' SIZE 10M REUSE     DEFAULT STORAGE (  INITIAL            2K                        NEXT               2K                        MAXEXTENTS        100                        PCTINCREASE         0  ); 

The DEFAULT STORAGE clause of CREATE TABLESPACE determines how space for tables will be allocated when tables are created without a STORAGE specification. Lookup tables are typically numerous and very small, so it might be desirable to create a default storage that will be sufficient to handle all lookup tables. The STORAGE clause can then be omitted from those CREATE TABLE scripts.

Performance Considerations

When you design the physical database, remember that performance is an important consideration. There are numerous factors related to the design that will affect the overall performance of the database. These factors include the data model itself, indexing, rollback and temporary segments, and the physical location of the data on the disks.

Page 639

NOTE

For more on performance-tuning issues, see Chapter 23, "Performance Tuning and Optimization."

A factor that can heavily impact overall performance stems from the logical model. The degree of normalization in the model often comes down to a trade-off between flexibility and performance. In the example of normalization presented in the section on the logical model, several relations were created that improved the flexibility of the model, as shown in Table 25.8.

Table 25.8. Junction table used to relate individuals to companies.

Individual-Company Relation	Companies
Individual ID (FK)	ID (PK)
Company ID (FK)	Company
	Company Notes
Entity Type
ID (PK)
Type

Separating company information from the Individuals relation added a considerable amount of flexibility. This allowed individuals to be related to more than one company, and it allowed addresses and phones to be related to either an individual or a company. Another nice feature of the normalized model is that it allows any number of phones and addresses to be related to an individual or a company, as in Table 25.9.

Table 25.9. One-to-many relationships.

Addresses	Phone Numbers
ID (PK)	ID (PK)
EntityType (FK)	Individual ID (FK)
Entity ID (FK)	Phone Number
Address Line 1	Phone Type (FK)
Address Line 2	Last Update User ID
Address Line 3	Last Update Date/Time
City (FK)

 continues 

Page 640

Table 25.9. continued

Phone Type

Addresses	Phone Numbers
Zip Code (FK)
Address Type (FK)
Last Update User ID
Last Update Date/Time
Address Type
ID (PK)	ID (PK)
Type	Type

A less useful feature of the normalized model is the separation of city and state, as in
Table 25.10.

Table 25.10. Separation of city and state.

Cities	States
Zip Code (PK)	Zip Code (PK)
City (CK)	State

The result is a very flexible (but possibly overly complex) data model. Assume, for example, that one of the primary uses of the database is to generate a listing of the names of contacts and their companies, addresses, and phone numbers. This is a fairly complex query and illustrates a potential flaw in the model: Although addresses and phones can be related to either companies or individuals, there is nothing in the model that allows phones and addresses to be related to an individual at a company. Assume that, as a workaround, a third entity type is created for an individual at a company, and a rule is imposed that the Individual ID is used for that entity where an entity's ID is required as a foreign key. The phone list can be generated under this scenario, but it requires joining nine tables. Individuals must be joined to Addresses, Phone Numbers, and Individual-Company Relation, which must be joined to Companies to get the company name , and Addresses must be joined to Cities and States. In addition, Phone Numbers and Addresses must be joined to Phone Types and Address Types to get their respective standardized type descriptions. Although joining nine tables is not a particularly difficult task, if the database contains millions of Individuals, the number of joins can have a very significant impact on performance. If this report is generated up-to-the-minute online, and the database has a high volume of transactions, the impact is further magnified.

Page 641

Denormalization, the opposite of normalization, can be used to improve performance under these circumstances. By combining some of the relations, the number of joins can be reduced without sacrificing flexibility. In Table 25.11, the listed denormalizations might be appropriate.

Table 25.11. Denormalization of City and State information.

Addresses	Phone Numbers
ID(PK)	ID (PK)
Individual ID	Individual ID
Company ID	Company ID
Address Line 1	Phone Number
Address Line 2	Phone Type
Address Line 3	Last Update User ID
City	Last Update Date/Time
State
Zip Code
Address Type
Last Update User ID
Last Update Date/Time
Individual-Company Relation	Companies
Individual ID (FK)	ID (PK)
Company ID (FK)	Company

With this model, only seven tables must be joined to generate the list, and no flexibility is lost. Note that the foreign key constraints must be removed from Individual ID and Company ID on the Addresses and Phone Numbers tables because one or the other might be NULL. Listing 25.3 demonstrates the SQL used to create the report after this denormalization.

Listing 25.3. SQL script that generates an address and phone list.

 SELECT First_Name, Middle_Init, Last_Name, Company_Name,        F.Type, Address_Line1, Address_Line2,        Address_Line3, City, State, Zip, G.Type,        Phone_Number FROM   Individuals A, Individual_Company_Relation B,        Companies C, Addresses D, Phones_Numbers E,        Address_Types F, Phone_Types G 

 continues 

Previous Table of Contents Next