I'm My Own SQL Episode 2: Defining Business Rules

by C. Hatton Humphrey

This is an edited version of the script for the I'm My Own SQL podcast Episode Two: Defining Business Rules. You can listen to the podcast at http://www.fusionauthority.com/Podcast/IMOS_Ep_2.pm3 and follow along with this article. Those who missed it can still catch the first episode.

Howdy folks. Thank you for listening to the I'm My Own SQl podcast. This is episode two, where we'll be analyzing business requirements, defining business rules and presenting a few more definitions for the future. I want to thank the folks at Fusion Authority for providing the hosting and bandwidth for this podcast.

If you have questions or comments for the show you can send them via email to immyownsql@fusionauthority.com or call them in to 716-514-4123.

To review from last week:

• We talked about the need to be able to communicate business requirements with the client.
• The initial communication is best done over a period of time when both you and the client can devote all of your time and attention to the requirements.
• You should end up with a verbalization, a paragraph form of the requirements that is agreed upon by the client and developer. You need something like that when building the application, anyway, but as a database developer it's extremely important.

Last week I asked you to have an example of a long-hand business requirement from a previous project ready to mark up. If you don't have that yet, get it out, have it printed and have a working pen or pencil ready to mark it up.

To illustrate what you need to do, we will continue the cookbook example that I talked about last week. The concept was for a site that would allow my wife to put her recipes on the web and a few other things.

Now, I'm going to condense the conversation to just the final revision, but this conversation took three different discussions sessions with my wife. In the end my final verbalization read as follows:

The client wants a site that will allow users to log in and either browse or edit recipes. Each recipe is made up of a list of ingredients and a set of directions. The ingredients are given in the form of an item, a quantity and a unit of measure. Directions can optionally be tied to an ingredient. Ingredients should include the average price and stores that they can be purchased at. The unit of measure should indicate whether it is a dry or wet unit of measure.

Based on what I have written there, it looks and sounds like I'm writing a program plan, not a database plan. While this is partially true, I'm leaving out the display, interface and reporting capabilities that were requested.

So we have this verbalization of a request. Great, now we can start programming, right? Cool your jets, code jockey; we're not there yet!

The design process starts with the verbalization and moves next to a list of business rules and requirements. To do this we identify all of the relevant nouns and verbs in the verbalization. We are actually summarizing what the verbalization says in bullet points.

Let's run through the sample that I'm working with so you can get an idea of what I mean. I am going to underline the words users and recipes in the first sentence. In the second, I will underline recipes, ingredients and directions. The third gives us ingredients, items and units of measure. I could go on with the rest of the paragraph, but you get the idea. These nouns are going to be used to identify the major components that we will store our data, known as entities in the relational database modeling system.

Once you've identified the entities, you have to define the relationships that they have with one another. A relational database requires that all entities (also known as tables) are connected using relations. For the first written part of our design process we're going to simply identify the verbs that are found to show the tie between two entities. In our example, I would be circling the verbs edit, is made up of, and are given in. Notice that I'm circling not just the verbs in some cases, but those words that make us understand the meat of the relationship.

Now that we have the nouns and verbs identified in our verbalization it's time to turn them into a bulleted list of statements. This list will drive our next step in the design process, where we actually use Visio or some other diagramming tool to draw our entities and the relationships between them and work our way through the normalization process.

I will start off the bulleting process by putting the first noun, the verb and the second noun from each sentence in a bullet. In some cases we identified three or even four nouns and the same verb. In these cases you are going to typically have the same initial noun and verb pointing to each of the others. For example, my first bullet is simply Users edit recipes. The next bullet is recipe is made up of ingredients, with another bullet for, recipe is made up of directions. Eventually I will go back and make all of the nouns plural and change the verbs to match but that is because of the naming conventions that I use.

Once I have identified all of the main noun and verb driven bullet points I go back through my list of entities (the nouns again) and define what each one of them is going to contain. This is what we will now call attributes and later call columns for the table. The first bullet for this concept will define the user. What information do we want to store about the user? This will be a communication point where we go back to the client in some cases or where we use our own judgment in others. In my case, the definition rule reads, users have a first and last name, email address, password and date last logged in. I make sure that my bullet points have the definitions for every single entity I have identified and also have those important verbal ties between.

In the end my bullet points read like this:

• Users edit recipes.
• Recipes are made up of ingredients.
• Recipes are made up of directions.
• Ingredients are given as items.
• Ingredients are given as units of measure.
• Ingredients can be tied to directions but do not have to be.
• Items are purchased from stores.
• Users have a first name, last name, email address and date of last logon.
• Recipes have a title, a date added and a user.
• Ingredients have an item, a quantity and a unit of measure.
• Directions have a narrative (used to explain the process) and a number to order the steps in.
• Items have a name, a store, a price and a type of measure (wet or dry).
• Units of measure have a name and an abbreviation.
• Stores have a name, an address, a city and a phone number.

I see that I'm running close to the end of my time, so I need to wrap up with another homework assignment. This week's assignment is simple; you need to create the bulleted business list from your verbalization. If you're going along with my example database you are in luck because the bullet points are already done for you. If you're not, then have them ready for the next episode where we'll continue on and build out a normalized database diagram by the end!

Thank you for listening! If you have questions or comments for the show you can send them via email to immyownsql@fusionauthority.com or call them in to 716-514-4123. I'll be back in a week to pick up the design process with some graphical aids. Talk to you then!

A Word document with the verbalization for the Recipe Database and the Bulleted Business Rules is available for download.

• Subscribe
• RSS

• Editorial
• Specials
• Community
• News
• Tech and Tags
• Views
• Reviews
• Techniques
• Security
• Best of ColdFusion Talk
• Knowledge Base
• Blog Watch

• Tipical Charlie