Welcome!

ColdFusion Authors: Yakov Fain, Maureen O'Gara, Nancy Y. Nee, Tad Anderson, Daniel Kaar

Related Topics: ColdFusion

ColdFusion: Article

The Relational Database: Center of Most Advanced Applications

Don't let proper database design remain a mystery to you

You won't get very far while building ColdFusion applications without the need for a relational database. Since ColdFusion is so easy to use, many developers come from non-programming based backgrounds, and proper database design remains a mystery.

Perhaps you've worked on other applications, such as a spreadsheet application in Microsoft Excel or used an Access database with a single table to store data. Both of these options serve their purpose, however they are not the best choice for web applications. In this architecture focus issue, I wanted to take some time to introduce you to relational database concepts.

Understanding the Terms
Before I start blabbing about database this and database that, I want to make sure you understand what I'm saying. So let's start by defining some common terms that you should be familiar with.

  • Database: A database is any collection of related data. It could be as simple as a grocery list or as complex as a full Enterprise Resource Planning application. The database is made up of many elements, starting with...
  • Tables: A table is similar to a single spreadsheet. A relational database is made of multiple tables. Tables are made up of columns and rows. A column represents a single piece of data, such as a name, zip code, or street address. A row represents a single set of all columns. No two rows within a table can be identical. You can relate tables together using ...
  • Keys: There are two types of keys in a relational database. A primary key is a column, or group of columns, that can uniquely identify a single row in the table. A foreign key is a column in one table that can be used to uniquely identify the row from another table. It is through the use of primary and foreign keys that you define relations between tables.
The act of designing a database is to take your data, and define the tables and relationships that you want to store it. This process is often called database normalization.

Database Normalization
Normal forms are a way to check that your database structure is correct. There are seven different levels of normal forms and each normal form exists to avoid a certain anomalies, that will not allow you to insert, or delete, data without also inserting or deleting a piece of unrelated data. You probably don't need to know specifics about the each level of normal forms. When developing a database, you just want to think about storing each piece of data only one time. You don't want duplicates. In your table structure, have you duplicated data somewhere? If so, you may want to re-work your structure so that you don't.

This will probably make more sense with an example. Suppose you were writing a program to catalog your CDs. You might start with a list of sample data, maybe something like Table 1. This is a table with four columns: Artist, Album Name, Genre, and AlbumID. The AlbumID is intended to be the primary key of this table. In a real application, you'll probably have a lot more data, such as a song list for each album, release dates, or the names of band members. We'll keep it simple for this example, though. If you examine the data, you'll notice some places where the data is duplicated. The artist name, Guster appears twice. The genres Grunge, and Alternative appear twice. If you were building an on-line record store, you would not want to store the genre or artist name a lot of times.

This table also exhibits insertion and deletion anomalies, which were discussed earlier. What happens if you want to delete the Bishop Allen album? You will also inadvertently delete the "Rock" genre. That is a deletion anomaly. What if you wanted to create a genre for blues? You wouldn't be able to do so without also entering an album. That is an insertion anomaly. You want to build your tables to avoid these sorts of issues. So, how do you do it?

Well, in this situation, you may first want to separate the Genre into its own table. You can see the genre table in Table 2. I added a GenreID column to the table. This is an integer column intended to be the primary key of the table. We can also split out the artist information into a separate table, as shown in Table 3. The table has two columns, a primary key named ArtistID and an artist column. With the artist and genre information moved into their own tables, what does your original album table look like? There isn't much left, just a primary key and an album name. However, you'll still want to preserve the relationships between the album, genre, and artist tables. How do you do this? You take the primary key of the genre and artist tables and put then in the album table. Our updated album table is shown in Table 4.

Why is this different? Well, it takes less disk space to store an integer than it does text. While you probably won't notice any problems in tables with just a few rows, the difference becomes much greater when you are dealing with larger amounts of data, such as a song database with a thousand records, such as iTunes. Each little bit starts to add up.

Database Relationships
There are really three different types of relationships that come into play when putting your data into tables. The first type is called a one-to-one relationship. This means that for any single piece of data A, there will only be a single piece of data B. If A, were a username, then B might be a password. For every username, there must be only one password and for every password there must be only one username. In most one to one relationships the data is stored in the same table. If for some reason you are splitting the data between two tables, you can represent the relationship by moving the primary key from either table into the other table as a foreign key.

The next type of relationship is a one-to-many relationship. This means for every piece of data A, there will be multiple pieces of data B. For every piece of data B, there will only be one piece of a data A. A good example of this is the artist/album, which we described above. For every artist, there may be multiple albums. But, each album only has a single artist. (For the sake of this example we are ignoring multi band compilations). You can represent one-to-many relationships by taking the primary key of the "one" side and put it into the table of the "many" side as a foreign key. This is what we did to split up the album and artist tables. Our example, above, created the genres and albums relationship as one-to-many.

The third type of relationship is a many-to-many relationship. This means that for every piece of A data there will be multiple pieces of B data, and for every piece of B data there will be multiple pieces of A data. Perhaps, you've got the Aerosmith album. It's rock. It's blues. Where do you categorize it? The Genre relationship to an album could be a many-to-many relationship. To implement this type of relationship in the database, we create a special type of table, often called an intersection or linking table. This table does not usually contain any data, only the primary keys of the two tables that it is linking. To implement a many-to-many relationship between genres and albums, we would use the GenreID from the albums table and create a new table, as shown in Table 5.

I know it looks like a table of numbers, and that's what it is. The primary key of intersection tables is usually made up of all the columns in it.

Common Database Design Mistakes
Before wrapping up this column, I want to finish off by pointing out some mistakes I often see beginner developers making. All of these are obvious in hindsight, but you probably don't realize you did it "wrong" until you have a problem and the light bulb inside in your head brightens up, and "Uh-oh" escape from your lips.

  • Names: You have a site registration, or an address book application, or something that requires you to collect and store the names of your users. Make sure that you store the first name and last name as separate columns in a database table, do not combine them. If the data is being collected, at some point you are going to be asked to do a mail merge. When you do that, it'll be a lot easier to do if you have the first and last names separate, so you can address people as "Mr Houser" instead of "Mr Jeff Houser."
  • Deleting Data: It's easy to write a delete statement in SQL, but what happens when your client or boss calls up to ask where their data went? If the data is truly deleted, you have no way to restore it. If you're lucky you have backup tapes, from which you can restore yesterday's data, but that can get messy. A better way is to not ever allow users to directly delete data. Create a Boolean field in your database table called Deleted. If set to 1, the record is flagged for deletion. If set to zero, the record is fine. You can run batch scripts on a routine basis to delete the data, as needed. When the user calls up to find out where their data is located, you can just flip that flag to restore it for them.
  • Store the Date: At some point, someone is going to want to look at the data you've been collecting through the web site. When are people registering on the site? When was the last time they modified their information? On most tables, I will add a "DateCreated" field to store the time that the record in the database was created, and a "DateLastModified" field to store the time the data was last modified. On some projects, I've had a few "higher ups" quite shocked that this data was not being collected.
  • Define Relationships: You should always make sure you use the built-in facilities of your database to specify keys, and define relationships between tables. In addition to helping you protect your data from inadvertent corruption, the database engine can often use these relationships and keys to automatically optimize queries. I've had quite a few sleepless nights trying to fix the invalid data that had resulted from deleting one piece of data, without deleting data that relates to it. For example, what if we deleted a genre, but did not delete the entries in our intersection table that related to that genre? Something is going to break somewhere.
Those were just some of the more common mistakes I've made myself or seen others make.

What Next?
The relational database is the center of most advanced applications, web-based or otherwise. Some of the more common databases used in ColdFusion development are SQL Server, MySQL, and PostgreSQL. Oracle often shows up on sites with larger load, and Access will sometimes show up on smaller sites. I would recommend putting aside some time to learn about the database of your choice, because many of the skills you learn can easily be transferred to all database platforms.

More Stories By Jeffry Houser

Jeffry is a technical entrepreneur with over 10 years of making the web work for you. Lately Jeffry has been cooped up in his cave building the first in a line of easy to use interface components for Flex Developers at www.flextras.com . He has a Computer Science degree from the days before business met the Internet and owns DotComIt, an Adobe Solutions Partner specializing in Rich Internet Applications. Jeffry is an Adobe Community Expert and produces The Flex Show, a podcast that includes expert interviews and screencast tutorials. Jeffry is also co-manager of the Hartford CT Adobe User Group, author of three ColdFusion books and over 30 articles, and has spoken at various events all over the US. In his spare time he is a musician, old school adventure game aficionado, and recording engineer. He also owns a Wii. You can read his blog at www.jeffryhouser.com, check out his podcast at www.theflexshow.com or check out his company at www.dot-com-it.com.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
One of the biggest challenges when developing connected devices is identifying user value and delivering it through successful user experiences. In his session at Internet of @ThingsExpo, Mike Kuniavsky, Principal Scientist, Innovation Services at PARC, described an IoT-specific approach to user experience design that combines approaches from interaction design, industrial design and service design to create experiences that go beyond simple connected gadgets to create lasting, multi-device experiences grounded in people's real needs and desires.
Enthusiasm for the Internet of Things has reached an all-time high. In 2013 alone, venture capitalists spent more than $1 billion dollars investing in the IoT space. With "smart" appliances and devices, IoT covers wearable smart devices, cloud services to hardware companies. Nest, a Google company, detects temperatures inside homes and automatically adjusts it by tracking its user's habit. These technologies are quickly developing and with it come challenges such as bridging infrastructure gaps, abiding by privacy concerns and making the concept a reality. These challenges can't be addressed w...
The Domain Name Service (DNS) is one of the most important components in networking infrastructure, enabling users and services to access applications by translating URLs (names) into IP addresses (numbers). Because every icon and URL and all embedded content on a website requires a DNS lookup loading complex sites necessitates hundreds of DNS queries. In addition, as more internet-enabled ‘Things' get connected, people will rely on DNS to name and find their fridges, toasters and toilets. According to a recent IDG Research Services Survey this rate of traffic will only grow. What's driving t...
Connected devices and the Internet of Things are getting significant momentum in 2014. In his session at Internet of @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, examined three key elements that together will drive mass adoption of the IoT before the end of 2015. The first element is the recent advent of robust open source protocols (like AllJoyn and WebRTC) that facilitate M2M communication. The second is broad availability of flexible, cost-effective storage designed to handle the massive surge in back-end data in a world where timely analytics is e...
Scott Jenson leads a project called The Physical Web within the Chrome team at Google. Project members are working to take the scalability and openness of the web and use it to talk to the exponentially exploding range of smart devices. Nearly every company today working on the IoT comes up with the same basic solution: use my server and you'll be fine. But if we really believe there will be trillions of these devices, that just can't scale. We need a system that is open a scalable and by using the URL as a basic building block, we open this up and get the same resilience that the web enjoys.
We are reaching the end of the beginning with WebRTC, and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) i...
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
How do APIs and IoT relate? The answer is not as simple as merely adding an API on top of a dumb device, but rather about understanding the architectural patterns for implementing an IoT fabric. There are typically two or three trends: Exposing the device to a management framework Exposing that management framework to a business centric logic Exposing that business layer and data to end users. This last trend is the IoT stack, which involves a new shift in the separation of what stuff happens, where data lives and where the interface lies. For instance, it's a mix of architectural styles ...
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrategies, will examine why IT must finally fulfill its role in support of its SBUs or face a new round of...
Cultural, regulatory, environmental, political and economic (CREPE) conditions over the past decade are creating cross-industry solution spaces that require processes and technologies from both the Internet of Things (IoT), and Data Management and Analytics (DMA). These solution spaces are evolving into Sensor Analytics Ecosystems (SAE) that represent significant new opportunities for organizations of all types. Public Utilities throughout the world, providing electricity, natural gas and water, are pursuing SmartGrid initiatives that represent one of the more mature examples of SAE. We have s...
The Internet of Things will greatly expand the opportunities for data collection and new business models driven off of that data. In her session at @ThingsExpo, Esmeralda Swartz, CMO of MetraTech, discussed how for this to be effective you not only need to have infrastructure and operational models capable of utilizing this new phenomenon, but increasingly service providers will need to convince a skeptical public to participate. Get ready to show them the money!
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at @ThingsExpo, Robin Raymond, Chief Architect at Hookflash, will walk through the shifting landscape of traditional telephone and voice services ...
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, discussed single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example t...
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
Bit6 today issued a challenge to the technology community implementing Web Real Time Communication (WebRTC). To leap beyond WebRTC’s significant limitations and fully leverage its underlying value to accelerate innovation, application developers need to consider the entire communications ecosystem.
The definition of IoT is not new, in fact it’s been around for over a decade. What has changed is the public's awareness that the technology we use on a daily basis has caught up on the vision of an always on, always connected world. If you look into the details of what comprises the IoT, you’ll see that it includes everything from cloud computing, Big Data analytics, “Things,” Web communication, applications, network, storage, etc. It is essentially including everything connected online from hardware to software, or as we like to say, it’s an Internet of many different things. The difference ...
Cloud Expo 2014 TV commercials will feature @ThingsExpo, which was launched in June, 2014 at New York City's Javits Center as the largest 'Internet of Things' event in the world.
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, phone and digital TV services to consumers primarily in rural areas.
"There is a natural synchronization between the business models, the IoT is there to support ,” explained Brendan O'Brien, Co-founder and Chief Architect of Aria Systems, in this SYS-CON.tv interview at the 15th International Cloud Expo®, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
The major cloud platforms defy a simple, side-by-side analysis. Each of the major IaaS public-cloud platforms offers their own unique strengths and functionality. Options for on-site private cloud are diverse as well, and must be designed and deployed while taking existing legacy architecture and infrastructure into account. Then the reality is that most enterprises are embarking on a hybrid cloud strategy and programs. In this Power Panel at 15th Cloud Expo (http://www.CloudComputingExpo.com), moderated by Ashar Baig, Research Director, Cloud, at Gigaom Research, Nate Gordon, Director of T...