Database Design Introduction
When your business outgrows spreadsheets and paper files for tracking and reporting essential information, it might be time to consider a fully functional database.
The right database design allows businesses to efficiently manage billions of pieces of information. Unlike spreadsheets or paper records, databases can track relationships between several sets of data, making it easy to create customized reports. In addition, database design provides the backbone for advanced web site functionality.
Many businesses depend on databases to:
- Track product inventory
- Generate prospect lists
- Manage customer information
- Maintain client information
- Power web applications
With the help of a database design professional, you can create a database that can improve your business' efficiency and speed, eliminate redundant data, and provide a clear perspective of how your business performs.
This BuyerZone Database Design Buyer's Guide will walk you through the decision-making process by exploring:
- What to look for in a database
- What types are available
- How to design a database that works for your business
- How to select the right designer to work with
- What the costs are
Designing Your Database Solutions
Before you sit down with a developer to work on database solutions, it's critical to have a thorough idea of what functionality you need. If you don't, you could wind up with a poorly designed database that wastes time, money, and company productivity.
Start by gathering everyone who will be using the database. This group should address questions such as:
- Why do we need this database? Outline your needs and what you intend to do with your database solutions. What information do you need to track? Will you use it mainly to store data or to perform complex reporting? What reports will you need to see? What existing software will it need to work with?
- Who will have access? You may need to pay a licensing fee for every person using the database, so it's important to know how many people will use it. Will you need to support multiple simultaneous users? Should it be accessible via the Internet? How much security do you need (passwords, log-ins, etc.)?
- What are your future plans? Will your business grow considerably over the next few years? How might it affect your data gathering or reporting requirements? If so, it's best to prepare for more functionality than you need so your database can adapt to changing requirements.
- Who will maintain the database? If possible, identify a database solutions expert in-house who can monitor the database once it's implemented. This way, if you run into a small problem, you'll be able to fix it in-house rather than turn to the vendor. If you don't have a qualified person on staff, identify a consultant or a support specialist from your vendor.
If you're really unsure where to start, a designer should be able help you define your requirements. By explaining what you're looking for in the finished product, a designer can craft custom database solutions for you or modify a set of sample databases they have already created. Selecting a vendor familiar with your industry can be valuable since they likely have developed databases similar to the one you want and know what questions to ask.
Once you have these answers, you can discuss your database development needs with a designer. Simply relay your overall needs including what information you need to track, how this information will be entered, and the types of reports you want to run.
Assuming you require a custom application for your database development, the designer will create a specification document that details the functionality and design you need. For lower-level details, such as the schema that defines what data the tables will store, the designer will put together a technical design document.
As the client, you should carefully read the full document and approve it only when you are satisfied that this blueprint will result in the application you want. As you review what will be created, make sure the flow seems logical and you can get the data you need with as few keystrokes as possible.
In addition, you should also make sure the database development only includes the key features you need. Many functions and applications that you or the designer dream up may seem interesting — but they are likely to go unused and add extra cost to your project.
With a completed specification, your application is ready to be developed. The database developer will work on his or her own or with a group of software engineers to create the tables and software code that enables the data to be created, added, modified, and deleted as necessary. A reporting system is also developed at this time.
Once developed, the designer will test the application by using "dummy data" or by importing your raw data into the database using delimited text or a spreadsheet file. This will demonstrate how the database treats your information and how the reports will generate. Inevitably, software glitches will come up that need to be fixed so it can be ready to be used by your company.
When it's time to roll out your database, the design team will install the program onto your server or wherever your database will reside. Your database designer may need to meet with your IT team to coordinate the best time for installation and testing. Before this project is considered complete, you and your team will perform a final round of user testing to catch any remaining bugs before relying on it for daily use.
Types of Database Programs
As a part of the project, you will need to choose the database program software on which your application will be developed. Selecting the right database programs for your business depends on several factors: the amount of information you need to store, the functionality you want, and the level of customer service you need.
- Desktop. For most small companies, desktop database solutions such as Microsoft Access, FileMaker's FileMakerPro, or Lotus Approach are often the right choice. They cost only a few hundred dollars and are easy to use. In addition, these database programs offer the advantage of having built-in interfaces that allow a developer to create a custom application using just the software.
- Enterprise-level. For businesses with significant database needs that require hundreds of thousands of records to be tracked, enterprise-level databases such as Oracle, Sybase, or Microsoft SQL are the most powerful database programs. You can even download an open source database like MySql which has the benefit of being free, you will still need to pay extra for product support.
These complex enterprise-level systems can handle billions of pieces of data, support multiple users at once, and offer increased security and reliability. They can get very expensive with prices starting in the thousands of dollars, but you do end up with a database program that fits all of your needs.
In addition to affecting functionality, your software decision also determines who you can tap to create your database. While the basic technique of designing database tables is shared across all programs, database developers typically have an expertise in actually developing on a more limited set of platforms. When choosing a developer, it's critical that they have extensive experience in the database programs that you use whether it's Access or Oracle.
Regardless of which platform is used, it's important to clarify any potential issues around ownership of the custom code that gets written using the software. Unless specified otherwise, the code is typically the intellectual property of the design firm.
Make sure you agree who owns the code before the project starts and learn what you're allowed to do with it when the database is complete. For example, clarify what your options are if you want to make changes to the functionality and want to use another developer. It may not cost extra to own the code, but if you establish who the owner is early on, it can save you tremendous headaches down the line.
Choosing Database Designer
The type of database designer you should work with depends on your current and future database needs. It doesn't matter if you use a small, local company with one or two database designers or a large national firm with a team of database architects, programmers, and designers. The right vendor will create a database that is user-friendly and meets your business needs.
First and foremost, look for database designers with extensive experience with the software you use (Access, Oracle, SQL, or other.) Although the process for designing a database can be similar across several platforms, a designer may do his or her best work using a particular program. By matching up database designers with the platforms they excel in, you increase your chances of developing the database that's the best fit for your business.
The right database designer should also have strong architecture prowess to design tables that will scale to your business. In addition, the designer also needs vast experience creating applications and database functionality. If one designer can't offer both attributes, look for database vendors that can provide multiple database design and application development experts that work in tandem.
Look for database designers or project managers with strong management expertise who will ensure the design process flows smoothly. The right database expert should pay strong attention to detail so all steps outlined in the specs and requirement documents are described clearly and created accurately according to your needs.
In addition to technical skills, reputable database designers should also possess other important qualities that show they are reliable and trustworthy:
- Strong communication — The designer should provide you with regular updates in language you can understand so you know the project is coming along to your specifications. And since the development process can be time-consuming and at times stressful, a designer that communicates well can smooth out feelings of tension and ensure you're happy with both the work and the end product.
- Integrity — If any flaws come up in the design or the development, the designer should stand up for his mistake and correct the problem at no additional cost.
- Honesty — The designers should be up front with you at all times. If your project hits a roadblock and will take longer than expected, they should let you know instead of trying a quick fix.
You should definitely look into multiple database designers before making a decision. Find out what different firms can offer, how they will approach your project, and what functionality they can offer within your budget. Ask as many questions as possible to learn about each designer's practices and processes before making a decision. Such questions may include:
- How long have you been in business?
- Is database design and development your primary business?
- What type of projects have you done that are similar to what I'm looking for?
- Are there sample databases you could show me that might be a good fit?
- Describe your process for designing and implementing a database.
- Who will work on my project?
- What training will you provide to my employees?
- Provide an example of a problem you helped a previous client overcome.
- Will I receive full documentation for how my database is constructed?
- If we need to modify the database in the future, will we have access to the code?
- Can you provide the type of database functionality I need for my business, or would I be better off with a prepackaged solution?
Always check references from previous customers, preferably in a business similar to yours. Your goal should be to find out as much as you can about the project to see if the database designer is one you would feel comfortable working with. Make sure to ask references if the project was completed on time, if the vendor addressed problems quickly, and what level of customer service the vendor provided.
Database Services Pricing
It's easy to experience sticker shock when shopping for custom-designed database services. Depending on the functionality you need, a database can cost tens to hundreds of thousands of dollars.
One way to approach the costs is to view database services as a long-term investment. It will cost a lot up front, but the end result will help your business run smoothly for years to come. If built properly, the time and productivity savings to your organization will more than recoup your investment.
Before work actually begins on your database services project, the designer will hold a session with you and your staff to discuss the project and gather your requirements. This session will clearly define what the database will do, how many employees will use it, how long the project will take, and what the costs will be. For small applications, this session may run one-to-two days; customized databases, like CRM systems, could take several weeks. After the session, the designer will write up the results in a specification document.
While this session can account for about $2,000 of your total costs, documenting the methodology is a critical step in the process. The spec document provides an agreed-upon set of detailed guidelines before starting work on your database.
Database design projects, which can take months, are typically billed by the hour. Rates typically range from $50 to $220 per hour depending on the size of your database and how much customization you need. The rate typically includes the initial development session, software licenses, programming, customer support, and employee training. More massive databases, detailed customization, and extensive customer support requirements will all push your project towards the higher end of that price range.
The largest part of this cost is time and labor. A small database can take 50 to 100 total hours ($2,500 to $10,000). Larger, more robust databases can take 100 to 2,000 hours ($15,000 to $400,000) or more. For customized projects that will take longer to complete, lower per hour rates may be available.
Changes to the original design are almost inevitable. If you need to make changes to your design after it's been implemented, you'll pay the same $50-$220 per hour rate. However, you may be able to negotiate a lower rate since the designer has already done the most intensive work.
In addition, you may need to license database software if you don't already have it in-house. Pricing may include a fixed number of users or require a separate license for each individual user for $50 to $200 or more, depending on the size of your database.
Get detailed estimates from each database services firm you're considering so you can compare them accurately - and don't be taken in by the lowest bottom line. You don't want to wind up with a designer that low-balled you with a favorable rate, only to find out the project takes far longer than expected and you're responsible for overrun charges.
Reputable designers typically guarantee their work against bugs or other problems for the first six months to a year. However, some also offer extended maintenance agreements. The price varies widely based on the level of support you need. Basic email and phone support can cost a few hundred dollars per year. Comprehensive database services agreements, with 24 x 7 support specialists and on-call repairs, can run 10% to 20% of the total project costs.
If you don't purchase a database services extended warranty, you'll pay an hourly fee of $100-$200 for each support or maintenance incident. The costs can vary based on location. Here's some pricing guidance we've collected:
- Midwest: $50/hour and up
- Small cities: $50-$150/hour
- Large cities: $50-$200/hour and up
Custom Database Design Tips
- Out-of-the-box solutions. Before seeking out custom database design, look for existing applications that can perform the functions you're looking for. Many custom database manufacturers may already offer databases tailored to specific verticals (example: automotive parts manufacturing). If your needs grow beyond what this type of database can handle, a designer can use it as a starting point to a custom database design solution.
- The more fields, the better. More fields in your custom database design provide better ways to sort your data. Breaking down large blocks of information into smaller pieces makes it easier to work with the data, obtain detailed reporting, and extract information.
- Database administrator. While it's valuable to have someone in-house that understands custom database design and administration, many small- to medium-sized companies may not be able to afford a dedicated IT team. You may want to weigh the costs of a full-time database administrator against a part-time consultant.
- Privacy in design. Avoid using sensitive data, such as employee Social Security numbers, when identifying employees in a database. It could violate employee privacy, particularly if multiple workers have access to the database.
- Backup. Find out what backup system your designer offers, whether it is an onsite storage server or offsite data backup. You need to ensure the information is protected in case of system failure or natural disaster.
- Track changes. Whether it's already built into your custom database design or something you need to specify for your application, it's a good idea to have the database track changes. Not only do modification histories let you pinpoint who made a particular change, they also track exactly what changes were made, allowing you to revert to the original if necessary.
- Source code. If you have the right to own and modify the source code, make sure you receive copies of the original files, not just files that can run your application but cannot be modified.