Database Design Essentials

This section introduces the basic concepts needed to construct a database. In the spirit of Java and JDBC, the information in this section is meant to be generic, covering fundamental knowledge applicable to all relational databases.

When shopping for a database, there are many factors to consider. For example, how much total disk space is needed? Be sure that the database server chosen can address the amount of data required. How fault tolerant does the database need to be? How well will the database perform under stress? How much data needs to be sent between the client and the database? If large amounts of data are retrieved from the database regularly, things can slow down considerably. How many users will be concurrently accessing the data? Which functionality/features of the database can be leveraged (such as stored procedures, caching, locking strategies, and API)? What about security? What about price? The list goes on and on. A good idea would be to make a spreadsheet, listing in each column the issues mentioned previously. Each row in the spreadsheet would correspond to each database being evaluated. Then, insert a number (say, between 1 and 10) to grade how each database ranks in each column, for each of the criteria. The total points earned for each database would give a general idea for overall performance and reliability. Taking this a step further, a weighting can also be applied to each column. This way, the computed total score for each database would be more influenced by those features most important. Spreadsheets and documents such as this are great for evaluating anything and make a fantastic communication tool between developers and managers. This approach is also well suited for determining if a relational database should be used at all. Simply add another category for each type of custom software being evaluated for data storage and compare it to the databases. Don’t forget to add a criterion for development time, if you are thinking of writing your data access software.

Design Patterns

Every project will have unique design challenges. Models, templates, and design patterns should be used whenever and wherever possible. Using a model helps to simplify design and abstracts complex tasks. Using design patterns also gives the code a standardized and consistent look and feel, making it highly readable and attractive for reuse. Custom design is generally required at some point during every project, but should be avoided when a clear solution can be found using a model. Custom design/implementation is likely to introduce bugs into the code as well. It’s a good idea to adhere to some set of guidelines when designing anything. Several good sources of design patterns are available both for general and Java-specific software design.

Database Normalization

Database normalization is the process of structuring the database tables in such a way that the data that is stored in those tables is balanced and organized so that searches are executed efficiently and deterministically. The basic idea is to keep related data together in logical groups. In the same way Java objects contain attributes that relate to that object, tables contain data representing a concept or type and are related via pointers, or keys. Data should be distributed across the tables evenly. Why? Each table should represent a related set of data. Avoid storing data redundantly across multiple tables. This practice not only will save disk space but also can reduce search time because there is less data to traverse. However, the most important reason to normalize data is for data integrity. If data is not normalized, any data that is changed has to be changed in several places. A denormalized database can sometimes be faster to query but more complicated to update and maintain. A normalized database will provide solid, reliable performance. Indexes should be used for bigger tables or for often-queried data, minimizing the time spent scanning through potentially large amounts of data. Also, the number of columns in a table shouldn’t be very high. If you are designing a database and find yourself putting in lots and lots of columns in your tables, it is often an indicator that your data might need to be reorganized. If a table is not only large but wide as well, it will slow down the search even more if searching for non-key or non-indexed columns. Creating indexes helps the RDBMS to resolve complex and resource-intensive queries because it doesn’t need to look at every row in the table.

Data should be logically grouped into tables, which are easy to manage and intuitive to relate. The result of a properly normalized database is one that is well organized and highly deterministic. Database normalization is typically performed through a progressive series of Normal Forms, the details of which aren’t covered here, but each form is basically a step or progression in refactoring the database to achieve a higher and higher level of normalization, balance, and stasis. A good rule of thumb to determine whether your data is properly normalized is to make sure that every column in a table is related to “the key, the whole key, and nothing but the key.”