Database Basics and Database Best Practices articles

Some thoughts on databases

Many companies start off with a desktop system on somebody's computer. Typically, it's Microsoft Excel or Microsoft Access. As it becomes increasingly apparent that these programs can't handle the company's database needs, someone takes the plunge and buys a database suitable for the enterprise. Maybe Oracle or SQL Server.

Three cheers! We're now all set with a database that does everything we want! Or are we? The typical problem with this upgrade is the company decides to save money at great cost. So instead of hiring a database consultant, some poor schmuck inside the company gets tasked with setting up the system.

But database design is a highly specialized area of knowledge, so the newbie makes dozens of mistakes including infrastructure type ones. These mistakes are very expensive to fix, a year down the road when it becomes obvious the situation thus unleashed is intolerable.

If your company is moving into a serious database, don't fall into the DIY trap. In home remodeling and repair, the DIY (Do It Yourself) problem often means it costs twice as much to complete the job than it would if a skilled tradesperson had done it from the start. If you don't have the specialized training and experience required for good database design, OK, adequate database design, don't attempt to do DIY the job. It'll only cost more that way.

But also realize not all database consultants are equal. Hire one the same way you'd hire somebody for any other job in your company. Look at credentials and experience. When interviewing the person, avoid the typical interview games and instead ask relevant questions. Your goal is to see if the person is confident (but not overconfident) and genuine.

Once the database is designed, that may not be the end of database work. When interviewing database consultants, ask for their general plan of attack for installing a system--what are the half dozen or so major steps to doing that? Then ask about ongoing work such as maintenance, repairs, and modifications.

Tuning, tweaking, optimizing

Get your database tuned, tweaked, and optimized by using Report Analyzer to look for anomalies. Originally designed just for Crystal Reports, Version 5 allows you to analyze any data source or file system.

Let's take a closer look at what Report Analyzer for Crystal Reports allows you to do:

• Automatically detect reports that do not take advantage of server side database processing.
• Detect record selection formulas which use data type conversion functions.
• Detect group fields which use formulas instead of SQL Expression fields.
• Find report tables which use less than an optimal number of fields.
• Discover special fields which cause an additional pass over the report data source.

 Improve report reliability and reduce maintenance effort

• Discover unused Formulas, Fields, and Variables.
• Identify group settings which cause blank pages and footers.
• Identify missing standard Crystal documentation best practices (Report Title, Author, Description special fields).
• Locate reports which may use the "Convert null field to default" setting inconsistently.
• Keep versions of your documented reports for historical comparison.

 Speed development and maintenance

• View report designs in a design window similar to the one in the Crystal Reports designer program.
• Select design objects and view/print settings, formula code (including conditional formatting formulas), dependencies with other design objects, and call stacks (for formulas only).
• Use interactive diagrams (trees) of formula hierarchies, object dependencies and report design elements.
• Query and print over 600 report settings across all documented reports. Perform instant impact assessment for database changes and formula changes across all documented reports.
• Locate which reports use specific functions, parameters, and design settings.

Improve report reliability and maintenance

Report Analyzer documents over 30 design and style settings.

Speed development and maintenance

You can easily view dependencies between report objects such as formulas, SQL expression fields, parameters, and database fields with the dependency viewer. Additionally, the powerful query feature lets you view dependency information across reporting projects.

Click to enlarge

Project Management

Report Analyzer generates comprehensive documentation of your reports, with over 100 presentation quality reports. Complexity metrics assist with test case planning and maintenance estimates.