More Database Design Mistakes To Avoid
Given that you need more information in or from the database, what do you do?
Do you create one of the following?
a new query
a procedure or function to extract and calculate from existing data
a field, or series of fields.
a snapshot / materialized view
Creating another database, when a schema would do:
Once I was asked to create another database. I thought that this was for a completely new system. In fact, it was closely aligned with the existing system. All it needed was a separate schema in the same Oracle database.
Result: Two databases to size, maintain, backup and recover. Slower performance over database links. After the fact, even though creating a new schema and importing the data would not have taken more than an hour or so, they still kept the dual databases!
Creating a table, when a function would do:
At another place, there were a lot of loads. And then, reports and queries were run against the loaded data after the loads finished. There was a “programmer” who was new to databases. Instead of sticking with this paradigm that he was already quite familiar with, he decided to be creative and do everything at once. He created a load, and then retrieved the data in one procedure. So, if you just wanted to retrieve the data, you had to run the load as well!
If this wasn’t enough, he also created a table for return codes.
So, he would insert a success or failure code into a table. And then read the table to see what the return code was.
A return code table might have made sense if the return code was needed for subsequent processing by other programs, but this was not the case.
All that was needed was a procedure that returned an OUT parameter.
Procedure_1 ( p1 in varchar,
P_rtn_code out number)
Or, a function with a return code.
Function_1 ( p1 in varchar)
Creating a series of tables and views when a single field would do:
At the same shop, another programmer wanted to exclude certain pieces of information (rows) from the web.
In programmer fashion, instead of consulting with me, he charged ahead and built something. In addition to the existing table, they now wanted a second table of the rows to exclude. And to make it work, they also made a series of three views that selected from the two tables.
They also speculated that “performance won’t be an issue”. However, they had no tests or evidence to make this conclusion.
Intuitively, this was bad design just listening to them describe it. I looked into what they were doing.
It turned out that the information they wanted to store, was actually determined by two fields. Which were already in the primary key of a particular table. One that they were already reading from! This was easily seen by looking at the very final view that they would use. And by listening to what they were saying.
“So, these two fields determine this final one right?” “Yes.” “Why not just add this single flag field to the existing table?” “We’ve already done it this way, and we will have to do it over. Well, we want to do it this way.” !!!
All they needed was to add a simple flag field to the existing table that had the two determining fields in the primary key. Update this field to Y, or N. When they retrieved, add a condition to the WHERE clause:
Where The_Flag = ‘Y’
The result was that they built this “design” and put it into production anyway. It ran pretty slowly. A few weeks later, the programmer came up to me and asked, “My program is running slow. Can you run optimizer to make it go faster?”. However, optimizer didn’t help much, and I knew it wouldn’t. Reading from a series of five tables and views, is of course going to be slower than reading a field from a single table.
Listen to your database expertise.
Use the correct object.
Rework bad designs.