To get optimum results with crosstab software, your work starts when you’re designing your database. In fact, it starts with the two-step process of ensuring your database has the proper data structures in place, which allows for appropriate data distribution among its tables. The two-step process consists of:
- Understanding your data, which we covered in a previous post
- Using effective normalization concepts, which we explore below
Database Normalization Concepts
Database normalization refers to the process of organizing data into tables in a way that lets you use the database to obtain unambiguous and intended results. A number of concepts can help to ensure this consistently happens when using your database.
Concept 1: Store Unique Information in a Single Location
Storing unique data in a single location, and then referring to it with identifiers as needed, is a highly efficient database strategy. An example could be customer records, which typically include information such as the customer’s name, address, email and phone numbers. When you’re creating tables based on customer records, you can use the customer ID to populate the tables instead of reproducing all the specific information contained in each record.
Using this strategy ensures any changes to the customer record only have to be made once in the main customer record location, rather than dozens of times across dozens of tables that contain the customer’s information. The information will instead be automatically updated across all tables that contain the customer ID.
Concept 2: Design Tables to Add New Records, Not New Fields
Adding new records to database tables is easy and free. Adding new fields, however, is typically complicated and expensive. An example here could be creating a table with a time-sensitive field, such as the specific year, which would require new fields to be included down the road.
Instead of creating a field that holds a specific year’s date, such as 2016, you’re better off creating a general “year” field. That way you can enter the specific year as needed as it pertains to each record, rather than adding a new field every time a new year needs to be included.
If you do need to display a specific year in a table, crosstabs can come to the rescue. Here you can set up a specific crosstab query that displays the desired years without having to amend or add any fields to your main database table.
Concept 3: Understand When Data Needs to be Duplicated
When you’re dealing with data that changes over time, you may sometimes need to de-normalize it to retain information you don’t want amended. An example would be a customer invoice that needs to keep an old customer address, even though the customer’s record linked to the customer ID was updated to a new address.
If the old data was not retained, you would lose the record of where the previous invoice was sent, which could transform into a significant business issue. Any time you need a snapshot of your information for historic purposes, make sure you design the option into your database from the onset. Once data is overwritten, there’s no getting it back.
While these three data normalization concepts are not the end-all for creating efficient and effective database tables, they do point you in the right direction for a solid start. Take your time designing and normalizing your database from the get-go and you’ll be rewarded with a database that easily supports applications using current as well as future data as needed and desired.