One of the things I like best about working with computers is designing systems, whether that’s programs, databases, interfaces or all three and more. Lately I’ve been working primarily on databases and I thought I’d talk a little about database design. At the earliest stages of database design, it doesn’t matter what database system you’re working with, it can be as simple as Access or MySQL or one of the more advanced systems like Oracle or, my current favorite, SQL Server. There are two primary starting points with database design: designing from scratch or redesigning an existing system. Let’s start with designing from scratch.
Ideally, you’ll get to do this step whether you’re designing from scratch or redesigning an existing system, but it isn’t as necessary if you’re redesigning as you can gather much of the information from the existing system if you absolutely have to. Either way, you’ll get a better final product if you can start with meetings with the business people and end users. When designing a database from scratch you’ll want to talk to both of these groups and to any programmers that will end up interfacing with the database you create. Take detailed notes on what they tell you they want in the database, but try to steer the conversation as much as possible to what they are trying to accomplish with the system. Business people and end users have ideas about what they want the system to do, but they will frequently give you much better ideas about what the system needs to do if they’re sticking to their area of expertise and telling you what it is they’re trying to accomplish and how the system will integrate with the business.
This is the time to sound them out on any ideas you may have about the project. Use your area of expertise to flesh out what they’re telling you and to compliment their knowledge about the business. This is also the time to begin to set expectations. If you don’t let them know what can be done, what is reasonable to do and the time frames that are involved in the various scenarios, you’ll end up with a botched project.
When gathering information to design a database, you frequently want to start with the scope of the database. How much of the business will it cover, how frequently will data come in and go out, how many sources will you have for the data and so on. You’ll get much of the next step while pursuing the first, but once the first step is well underway you can concentrate more on the second step: thinking about how the data is grouped and the specific pieces of information to group. This is what will eventually become your tables and columns. You’ll tend to get the information grouped the way the presenters of the information think it should be grouped, but keep in mind that you’ll likely want to rearrange it all when putting the database together. This is also a good time to be gathering the terms they use for their data and what those terms really mean. You won’t always want to use their terms for column or table names as business terms can be overly specific.
When talking to the business people and end users, talk about the future of the data. How do they see it being used as time goes on? What expansion might occur? Being prepared for these developments not only makes it easier on developers in the future, it often lets you better arrange the data now. As you’re having these conversations and taking these notes, you’ll be learning their business. The better you know their business, the better the system you’ll create however, you have to make sure you keep a step or two back so you can look at the data without being locked in to the processes they currently use. When they’re telling you the details about what they want to do with the data and what all the data is, they’ll be looking at it through processes that they’ve been using for a long time. Frequently those processes are dictated by the systems they’re using and that’s exactly what you’re trying to get away from. This is why you want to focus more on what needs to be done than how they currently do it. I’ll talk more later about ways to ease into talking to the people you’re working with about changing the way they do things, but while you’re doing the information gathering, don’t let that bother you. You’re trying to make the best system you can to start with, you can always compromise later if you have to.
After you’ve done all the gathering, you’ve finished the “generic” part of the design, now you have to take your system into account in determining your column types, where you want to store the data and even if you want to store the data. When I talk about that I’ll use the examples of Access and SQL Server.