Chapter 17

Ten Database Design Do’s

In this chapter, we focus on some of the mistakes and shortcomings we’ve seen in Oracle databases over the years. Most of these are honest mistakes due to inexperience with Oracle or databases in general and can easily be overcome. After all, if it weren’t for issues like these, DBAs like you wouldn’t have anything to do!

Constrain Your Data

Constraints enforce rules against your data. Oracle offers some of these built-in constraints:

Primary keys identify a column or columns in the table whose data for the values stored is unique and non-null.

Foreign keys enforce something called referential integrity.

Check constraints are customizable constraints that check the data entered into a column.

Not Null constraints disallow an empty column to be empty.

Unique constraints are a column or group of columns whose values together are unique for the row.

Constraints are a very useful and almost required feature in any database. Odd as it may seem, some software vendors don’t natively include a system of constraints in the database software. This situation requires developers to code their own constraints in the application. This approach can be extremely difficult and a nightmare to maintain. Not to mention the fact that they will be enforced only through the application itself. Anybody gaining access via a tool such as SQL*Plus won’t be required to obey the application constraints.

In some Oracle databases, the designer or primary developers came from a database that required the constraints to be created and managed in the application. Make sure you don’t fall victim to this situation: Use the built-in Oracle constraints.

Spread Out Your IO

When laying the files down on your system, you should make sure to evenly balance the files across the available disks. Some people might argue that their hands are tied.

For systems using local storage inside the server, buy as many smaller disks as possible so you can balance your IO (input/output). These days, it seems that manufacturers are offering ever-larger devices for storage, which makes your job difficult. Keep in mind that when you work with the storage/server vendor, the machine is for storing and retrieving data. By having several locations to store your data, you get more tuning capabilities.

The rules for storing Oracle files can be broken down in an infinite number of ways. Here are some basics:

Separate tables and indexes across different drives.

Store your redo log groups and members separately.

Store extraneous data unrelated to application data separately.

Store table partitions separately.

Store system files separately.

Store the Oracle binaries on their own device.

Store backups separately.

Use storage performance tiering to separate your data (high-value data on the fast/expensive disks; low-value data on the slower/cheaper disks).

You can break down your storage system even further depending on your data access behavior. By using the Oracle data dictionary and available monitoring tools, you can fine-tune the storage layout for your specific application.

Know Basic Data Modeling Skills

Data normalization is how you lay out your application storage needs in your tables. Before you begin designing a database from scratch, know the rules of normalization.

Some developers fall victim to data normalization shortcomings because of previous experience with other databases. For example, we’ve seen people design data models based on their experience with Microsoft Excel or Lotus Notes. These flat file-type databases have different rules for design. Normalization is a set of rules designed for relational type databases. Spend some quality time with an Oracle data-modeling book or class before you get too far in an application design project.

Use Naming Conventions

This topic boils down to good data-modeling skills. When you’re creating objects in your system, it’s important to follow rules. If you do your research or take a class on data modeling, you know the guidelines. Best practice is to adhere to those as best as possible. However, the most important thing is to follow some sort of documented, repeatable guidelines everyone can easily understand.

Avoid these common mistakes:

Don’t use keywords. Don’t name your table table.

Don’t let the system give default names. This happens often with constraints. Take the time to come up with something descriptive.

Don’t use quotes with column names and table names. Many over-the-counter developer tools do this. The problem is that you can end up with objects that have mixed-case names. Next thing you know, you have different tables named EMP, emp, and emP.

Create a document that outlines the standard practices your company will use. This table will aid in the training of new hires and make sure the IT department is on the same page when working collaboratively on an application.

Watch Your Roles and Privileges

Make sure you don’t fall to temptation and take the easy way out when configuring object access. Don’t grant everything to everybody because the design team doesn’t want to put a system of roles and privileges in place. Taking the easy way is especially tempting when you need to meet a project deadline and a developer is dead in the water.

Do your best to come up with different roles for your developers, application users, and application owners. This effort makes management and security much easier down the road. Chapter 10 deals with configuring roles and privileges in your database. That chapter is very important not only for security but manageability.

Always question people when they ask for privileges. Most of the time people ask for way more than they need.

Did you know that poor role and privilege design can also add to performance problems? It’s true. Every time someone runs a SQL statement, all the privileges for the person and objects involved have to be examined. When people have more privileges than they need, more internal rows have to be examined when Oracle is figuring out what they’re allowed to do. Examining these privileges may add only milliseconds to SQL execution, but as numbers of users executing SQL compound over time, the extra overhead of examining privileges takes away from memory and CPU resources.

Axe Ad Hoc Queries

Okay, getting rid of ad hoc queries is difficult advice to swallow. Dozens of companies offer tools that show managers how easy it is to go into a database and design all kinds of fancy reports with graphs and colors. They promise increased revenue and efficient information transfer. The problem is that these products are marketed to managers, not the technical team.

We aren’t saying that there is no place for a tool like this. Quite the opposite. After all, the whole point of having a database is to serve up data. However, having some control over what type of reports are allowed is going to make the DBA, the system administrator, and the end users happier overall.

If you allow unsolicited ad hoc queries in your database to run any time, in any form, you’re asking for trouble. Not only can they run slowly, but the entire database can become unusable during their execution.

In an ideal world, reports are designed, qualified, and approved before they’re run. A team of developers can work with DBAs and managers to list information needs. Those can be skillfully and efficiently transformed into canned reports available at the click of a button. They can be scheduled to run at specific times to avoid impacting the system.

Enforce Password Security

When you create a user in Oracle, you’re forced to set a password. This is good. However, not until 11g did Oracle force you to make sure that password was secure. During database creation, Oracle asks whether you want to revert to the pre-11g security requirements, which were minimal. Luckily, in 12c, Oracle doesn’t ask you anymore. Instead, it forces you to use a more secure approach out of the gate. In any case, if you’re new to a system, one of the first things we recommend is an overview of the password security system.

Oracle password profiles remedy the following common problems easily:

Lack of password complexity

No regular password expiration schedule

Reusable passwords

Shared logins

Default passwords

Implementing password security through a user profile is extremely easy. Unfortunately, one of the side effects is that the users will hate you for it! People seem to hate having to remember a password that’s something other than their username. However, don’t let the moaning of your colleagues sway you. Strong passwords are critical. You don’t want your company being the next one vilified on CNN because of a security breach.

Avoid Having Too Many Cooks in the Kitchen

Make sure that only company-approved people have access to the DBA role on the database. Not only does having unapproved DBAs threaten security, it also reduces accountability. Tracing problems is much easier when the source isn’t a group of dozens of users. Most problems we encounter are due to user error because someone did something with a privilege without understanding all the consequences.

Again, expect resistant users. Every company has that person who’s been with the team for 20 years and knows the business and application inside and out. He has always had DBA access. The problem is that he hasn’t had any formal Oracle training or experience.

Do what you have to do to convince management that few people should have the keys to the kingdom. Also, make sure those people who do have the keys are trained and accountable for the actions they take. If you present your case correctly, people should be thanking you. You’re removing untrained people from the pool of people who are going to have the finger pointed at them when problems occur. Think of it this way: As a DBA, you do not want root access. Sure, that is at times inconvenient, but, you also don’t want your name in the hat when problems occur on the OS and blame is being assigned.

Package Code

SQL is the primary language for accessing and manipulating Oracle data. You can embed it in applications or store it in the database in the form of stored procedures.

Not all SQL needs to be stored in the database, but consider designing the application so the bulk of the business processes is made up of stored code.

Unless the developers are trained to take advantage of Oracle’s stored procedure mechanisms, you don’t have the best, most efficient database possible.

Stored code enforces security. A stored procedure can be encrypted (wrapped, in Oracle terms). Users can run procedures without access to the base objects with which the code works. Stored procedures are executed with the owner’s permissions, not the user who is calling it. When you store code, a user does not need a privilege to UPDATE or DELETE from a table. The user just needs access to the procedural code that does the UPDATE or DELETE for them. That way, he or she cannot access the table outside the program to modify the data. Heck, the user can’t even see the table.

Stored code performs better. It is precompiled in the database and can be stored in memory without parsing and compiling. This situation decreases CPU usage and increases system scalability. Stored code is easier to maintain because it’s in one place. If you’re adding functionality or changing business rules, the application can immediately take advantage of the changes without releasing a new version.

Test Recovery Strategies

With a little training, it’s easy to design a backup process for your system. Testing is a key element to a robust backup and recovery strategy. However, running an error-free backup every night doesn’t mean you can recover with it.

Now, we don’t want to be doomsday preachers. Using RMAN and getting backups with no errors mean you have a significant chance of recovery. But what if you need those backups and they don’t work? You could be, as they say, caught with your pants down. You might even be out of a job.

This advice about testing extends to training. Do you know what commands to issue for a recovery? Do you know the fastest way to recover given the specific type of failure? Will you use RMAN or Flashback Database?

Testing your recovery strategy checks the backup itself and lets you practice for situations where a speedy recovery is required. You don’t want to spend an hour reading the Oracle documentation when you’re in a pinch. Know how to reduce the liability of your skill set by testing and practice.

Also, harkening back to the preceding chapter, document your recovery process. You may not be the custodian of the databases forever. Someone will come along after you. Giving other people a leg up preserves your legacy. Or, you can think of it purely selfishly: What if you have to do a recovery and you haven’t practiced for over a year? How much of your time is going to be spent figuring out the right commands to run? How will you get the backups from tape? If you have a playbook laid out, you can significantly reduce your mean time to recovery.