You’re an old pro at developing databases, right? You know the ins and outs, and even the shortcuts that’ll save you time. But will those shortcuts really save you? Or will they eventually, one day, all come together and cause your database to crash?

It’s a possibility.

While you can take your chances, you can also take concrete action and read these 8 tips on avoiding common database development mistakes. They may actually just save you time in the long run. And that makes for a happy database developer, no?

 

 

How to Avoid 8 Common Database Development Mistakes

Common Mistake 1. Poor Naming Standards

We’ll start with the simple and obvious – naming standards. While everyone seems to know that poor naming standards cause a variety of issues, the vast majority don’t adhere to proper standards, at least not all of the time.

Naming standards are a matter of personal choice. However, you need to make sure that your decisions are consistent, logical, and well documented.

1. Consistent: If you’re working on a customer address field, you shouldn’t write it differently. For example, customer address shouldn’t be represented by both custadrs and customeradress. Choose one, stick to it, and document it.

2. Logical: If your naming standards are well documented, there may be hundreds of pages of documentation. While it’s important to document your work, no one wants to read hundreds of pages of documentation every time they come across a different name. No one.

As a result, make sure that your naming standards make sense. For example, end names with _date for date columns.  Future programmers have to figure out your method, and this system makes it easy for them.

 

 

Common Mistake 2. Misuse of the Primary Key

Many don’t seem to know how to use the primary key. They forget that:

1. You don’t base the primary key value off of the data in the row

2. The value shouldn’t have meaning and, as a result, application data shouldn’t be used.

3. Primary key values should never be changed

4. Primary key values are values sequentially or randomly generated and managed by the system

Primary keys need to have these properties, so that you can move data from system to system or so that you can change the underlying data without interfering or complicating relationships.

 

 

Common Mistake 3. Poor Documentation

It may be a no brainer, but it’s still an issue that needs to be addressed. All naming standards, as well as definitions of tables, columns and relationships must be kept in a document that all current, as well as all future, programmers can access.

It’s not enough to have documentation with definitions alone, though. You have to spell out how you expect your database structure to be used. While it may take time, it’s better to have your bases covered than to have the database collapse.

 

 

Common Mistake 4. Overusing Stored Procedure

How often are you using stored procedures? A lot? A little? While there are certainly times when you should be using them, relying too heavily can cause issues. For example, if you want to make a change to a stored procedure, you most often have to write a completely new one. Why? You don’t know which systems are currently running that stored procedure. With multiple versions, it’s hard to keep straight which version is which.

To prevent these kind of stored procedure issues, use the advanced ORMs. In fact, doing this will make your process more efficient.

 

 

Common Mistake 5. Improper Normalization

Normalization is all about relationships and how you organize your data into tables. While some people are all about normalization, and even err on the side of overdoing it, others don’t do nearly enough.

Make sure that you’re in the middle.

General rules? If your data is shared among multiple rows, keep it in the same table if the change in one shouldn’t affect the other rows. However, if the change in one should affect the other rows, the data goes to another table.

 

 

Common Mistake 6. Not Using Appropriate Indexes

As with normalization, make sure that you are using the appropriate amount of indexes. You can run query analysis to help you decide how many indexes are needed. You may also check server performance to see how locking indexes affect it.

Outside of testing this, some general guidelines are that:

1. Foreign keys should have an index

2. WHERE fields should have an index

 

 

Common Mistake 7. Hard Deletes

If you’re anything like me, you delete something only to realize down the line that you need it. Retrieving it is suddenly crucial to saving several hours of your day.

Sound familiar?

That’s exactly why you should perform soft deletes. With soft deletes, you’re marking a row as inactive, and can retrieve it at a later time. With a hard delete, you’ll spend hours searching through transaction logs but soft deletes save you time.

 

Common Mistake 8. Using Exclusive Arcs Incorrectly

Exclusive arcs add greater complexity, which often lead to database development issues. As a result, you should only use exclusive arcs in certain cases, and in those situations, arcs can only be used in these circumstances:

1.  If one relationship in the arc provides the primary key, and each of the other possible relationships can as well

2. If the relationships in the arc all have the same optionality

3. If the arc is being used for only one entity

4. If the relationship is only in one arc

 

 

If you push yourself to better documentation and naming standards, as well as remember the simple rules surrounding appropriate indexes, exclusive arcs, normalization, and primary keys, you’ll be in great shape. You’ll avoid all of those time consuming mistakes that make you wish that you were anything but a database developer.

 

What other mistakes are made during database development? How do you avoid them? Let us know in the comments section, or join the conversation on Facebook, Twitter, and LinkedIn.

 

Thanks to youngthousands for the use of their respective photographs.