You’re getting ready to design a database from scratch. What do you really need to include? What steps do you really need to take? Are there shortcuts that you can take or steps that you can skip? I can’t really speak to this last question because I don’t know what your process is when creating a database, but I can speak to the components that are absolutely necessary for a successful database project. There are 10 essentials:
- Understand the database’s purpose.
- Get the right tool.
- Gather the requirements for the database.
- Be conscientious when modeling the data.
- Enforce relationships.
- Use the appropriate data types.
- Include indexes when modeling.
- Standardize the naming convention.
- Store the code that touches the data in SQL Server.
- Document the work.
1. Understand the Purpose
Despite the popularity of Scrum, rapid application development (RAD), and numerous other rapid-development techniques, you still need to know why you’re creating the database. The first essential is that you discover and understand the database’s purpose before doing anything else. Saying that the database’s purpose is “to store records” isn’t good enough. You need to understand the business reason for this database’s existence before it goes into development, much less production. The business reason needs to map not only to the technical specifications but also to the business purpose and the business processes that this database will be supporting. For example, will it be a retail point of sale (POS) database, an HR database, or part of a customer relationship management (CRM) package that will help you track customers and manage the sales cycle? You need to know this and many more details about why you’re creating the database before you start the design process.
2. Get the Right Tool
You can’t create a viable database without software tools, no more than you could build a house without construction tools. So, you’re going to need data modeling software. Data modeling software, also called a CASE tool, is the data modeler’s and data designer’s best friend. A good CASE tool can help you quickly create different types of data models—such as functional decompositions, system and context diagrams, business process and data flow diagrams, and entity relationship models—each of which will help you communicate to your colleagues the visual components of the proposed system you’re building. Some CASE tools have features that enable sharing, coordination, merging, and version control for design teams. Last, but certainly not least, these tools effectively document what you’re doing and, by implication, why you’re doing it.
The cost of entry into the CASE tool market is not insignificant. However, the initial investment will be paid back in terms of shortened time-to-market for database projects and increased knowledge of corporate data and processes. You can read about six different CASE tools in “Comparative Review: Sizing Up Data Modeling Software”. The comparison is based on capability and price, with some indication of how long it might take you to learn the package.
There is one thing to remember, though: No modeling tool will ever replace a person who understands how the business works. The CASE tool will only assist in creating visual representations of the business processes and structure.
3. Gather the Requirements
Once you understand the overarching reason why you’re doing the database project and you’ve selected a tool that will help you visualize the as-is and to-be environments, you need to do a deep dive into requirements gathering. You need to understand the requirements well enough to be able to create visual representations of data, processes, and systems.
When gathering the requirements, don’t limit yourself to disk drives, virtual machines (VMs), and other technical requirements. Although you need to know the technical requirements, they aren’t pivotal to good database design. The database that you’re creating has to support business operations, so you need to interview company staff members and managers to understand how the business runs. Only then can you get a handle on the requirements that you’ll need to meet in order to create a viable database.
When you’re gathering requirements, you’re going to encounter conflicting business needs—count on it. You’ll have to wear your diplomat hat to get the parties involved to agree on some sort of compromise so that the project can move forward. For more information about gathering business and system requirements, see “Data Modeling”.
4. Be Conscientious When Modeling the Data
My favorite part of database design is modeling the data—that is, creating structures that will hold distinctly different data sets (entities or tables) and representing the relationships between pairs of these data sets. When you’re modeling the data, you’ll have a chance to reaffirm or correct what you found when gathering the requirements.