Designing the Data

A well-designed database is the result of careful data modeling to identify the data and data handling services your application requires. The process of data modeling includes:

• Identifying the data (what the data is and where the data is located).
• Defining the data (assigning tables, columns, and data types).
• Ensuring data integrity (providing referential integrity, business rules, and data validation).
• Defining the operational processes (specifying ongoing operational processes such as backup and performance monitoring).
• Choosing a data storage technology (selecting from the differing kinds of data storage technologies, including relational, hierarchical, and indexed).

Data modeling is very important. Most of your application's database access speed will come from careful modeling of the data structures, handling multiuser concurrency situations, and optimizing queries.

If you're used to building client/server applications using local databases, data modeling is not as simple as it appears. Data modeling is especially difficult when it comes to identifying and resolving multiuser concurrency situations.

In a large distributed application, it is quite common to have dozens (or even hundreds) of users all accessing the same database. Worse, they may be accessing the same table and identical rows — a situation called concurrency. Concurrency has to do with separate processes, running at the same time, trying to use the same item. Concurrency problems are generally resolved by adjusting transaction sizes (smaller is often better), reducing result sets (smaller is better) and using differing types of locks to maintain data integrity.

Concurrency is generally thought of as a design problem for just the clients that use your single application. But what if your application uses a transaction that spans several tables and some other application does the same? This type of concurrency problem is one of the most difficult to identify and prevent. It basically requires that you understand the entire dynamic transaction workload as presented to the database at any point in time.

You should take the time to carefully design your database. High-performance data access depends on the good data structures and relationships that result from attentive data modeling.

For More Information   For more information on data modeling, designing data structures, and determining data attributes, see Designing Enterprise Data in Chapter 4 of this book. For more information on how your application can handle concurrency issues by using locks, see Managing Concurrency With Cursor Locks in Chapter 7 of this book.