Database Design Thoughts for Packaged Applications

By Bill Graziano on 26 October 2003 | Tags: Database Design

Over the years that I've worked with databases I've worked with many packaged applications. Some were commercial applications and some were open source applications. Typically I'm trying to put data in, get data out or just figure out where the data went. So I've compiled a small list of things I wish developers would do when they build database applications (and a rant of things I wish they wouldn't do). Some are little things and some are big things. All of them will make the life of the DBA that uses your software better. And it's ALL about the DBA!

I don't expect everyone to agree with all these. Everyone has their own likes and dislikes. That's why there's a link at the bottom to comment on what I've written. I've to provide a reason for each suggestion. The biggest thing I'd ask of developers in consistency. Even if you follow bad standards, follow them consistently. This list isn't complete. It isn't even a solid set of database development standards (though it may become one soon). It is a list of good database practices to ease the integration of applications into an existing environment.

Objects

First, prefix all your object names with a prefix indicating they are part of your software package. If I'm going to install your tables, procedures, views, etc. into my database along with all my oddly named objects I want to be able to find them easily. Snitz did a great job of this. All their tables have a "FORUM_" prefix. In their case that prefix was even configurable in the software. Note: Which was only possible because they didn't use stored procedures. More on that later.

Typically I prefer short prefixes to longer ones. I also prefer tables to have a singular name rather than a plural name. Stored procedures, views, etc. should have the same prefix as the tables. Please don't use usp_ and definitely don't use sp_ to prefix stored procedures. In fact, don't use prefixes for objects types at all -- even for views. Enterprise Manager already separates the different types of objects for me. Find another to name views that doesn't involve a prefix. I'm fine with putting the word "view" at thend end.

In Enterprise Manager I like to start typing the object name in the list of objects. EM will scroll down based on what I type. With good prefixes and naming standards I can quickly find the objects I'm looking for.

For stored procedures, functions, etc. that primarily refer to a single table I try to name them using the object name and then the action. So the stored procedure to add a topic in a forum database might be FORUM_TopicAdd. This groups all the stored procedures that reference the topic table together in the list.

Object ownership can become a tricky issue. Especially in shared SQL Server hosting environments. When I install applications on servers I control (i.e. I'm the system administrator) they get installed owned by the DBO. When I install into a shared SQL Server environment they get installed owned by my user -- even though I'm a DBO. If you specify "dbo.objectname" in the create scripts you'll get DBO-owned objects. I don't really care which way you do it, just be consistent. Definitely test your application where you're a DBA but not a system administrator.

Columns

Consistency is especially import on column names. Whatever standards you pick for datatypes and abbreviations should be rigorously followed. If you have datatypes for amounts, don't use "money" in one and decimal(10,2) in the next unless you have a really, really good reason.

There are cases where packages choose odd datatypes. If you do, try to include some functions to convert it to standard values. The Snitz forums run on Access, SQL Server and MySQL. They use a character field to store dates in YYYYMMDDHHMMSS format. It would be great if they included a user-defined function or view that could convert that to a standard SQL Server datetime value (and the other way around). I ended up writing my own when I converted to their forums -- which are great by the way!

Follow your abbrevation standards. There's nothing quite like joining CustomerNum to CustomerNbr to make you nervous about using someone's code. I do try to name primary keys and foreign keys the same whenever I can. For example, try to avoid a join like authors.id = books.fk_author.

I also prefer using mixed case for column names such as CustomerNumber. I don't like all upper or lower case. I also don't like putting the datatype in the column name.

Table Design

Please follow good table design standards. This article is too short to list them all but we do have a list of articles for you. At the minimum use primary keys and foreign keys with referential integrity. Please put the referential integrity at the database level and not buried in your application code. It's much easier to figure out that way.

While you're at it, add defaults and constraints where you can. That makes it much less like for me to add invalid data when I'm writing code that integrates with your tables. Also, add indexes on commonly searched and sorted fields.

Personally I don't mind using identity columns as primary keys if nothing else is available. I know others have different views on this.

Architecture

If you really expect people to use your application and interact with it build an API in the database. This may sound simple but it's really not. Well designed stored procedures should be able to provide a data modification API to your database. All updates your application makes should run through these procedures. That way any other application that needs to change your database can use these procedures.

This database API should be at higher level than one procedure per table. For example, in Snitz adding a new topic involves adding the topic, updating the forum table and updating a stats table (if I remember correctly). All those can be done in one "AddTopic" stored procedure.

You can provide the query side of the API through views and user-defined functions. Recall that user-defined functions can take parameters and return a table value.

I'd prefer that your application didn't use triggers unless it absolutely had to. I may have to add triggers to your application to get it to integrate with what I've already got and I don't want to be wading through your triggers to do it. Of course, if you've designed a nice, clean database API then I can just modify that.

When you're writing SELECT statements, please prefix each column with the table it came from. There's nothing like pulling up a query that joins eight tables, finding the field you want and then trying to figure out which table it can from.

Summary

Those are some thoughts I've had ticking around in my head on how I'd like to see application developers approach databases. They typically spend their time on a database design. But don't always consider how easy that design will be to use or integrate into an existing application. Maybe this article will make my life easier in the future.