Designing Situational Applications - Data Modeling

Posted: 6/3/2009 5:13:36 PM
This post in the Designing Situational Applications will help you understand an importan concept in building any kind of application: Data Modeling
This post is part three of a series. You may want to begin with:

Here I want to pause with our Customer Extranet and build some background. This is the kind of thing computer engineers learn on the job. Some university programs have courses on it. If you don't know what data modeling is, your brain is about to explode. You might want to read this article about How To Learn About Everything. It's normal to be confused at first.

Now, ask yourself, do you want to take the red pill, or the blue pill? If you continue, you will learn how to think like the guy in the white suit at the end of Matrix Revolutions.


Let's go...

Data modelling videos

Let's start with some videos. Youtube EDU has some lectures by professional educators. You may want to watch these at home or on the weekend... a few times.

If what you are hearing is "over your head," don't get discouraged, thousands of people do this kind of abstraction every day around the world and there's nothing about those people that you don't have. You will need patience and courage to get through it, just like developing any skill. It's worth it, it's a skill you'll have forever. It will change your life.

There is a reason database architects earn $97,000 a year. Do you want to pay that database architect $97,000 a year or do you want to learn how to do what that Database Architect can do? It takes practice and energy, but you can do it. I never took a database modelling course at university, I learned it all on the job. I made a lot of mistakes, but now I could model your entire life with Qrimp. It is possible.

Data modelling reading and community

I'll start with the Wikipedia Article about Data Modeling. There is a lot of information there and it is edited by professionals. Don't get discouraged if you don't understand it.

If you'd like to read more about data modeling, see some examples, and join a community, the best resource I have found is run by a very, very nice gentleman from the UK named Barry Williams. Barry started out with his own website called Database Answers. He has a huge library of example data models and he runs a The Database Answers Social Network at Ning. Be conscious of his time. Lots of college kids go in there and ask him to do their homework. Use the forums to ask for comments on your data model, don't ask them to do your data model. He may already have a data model you want in his library of data models (there are hundreds in there). Some of those data models alone could cost you thousands of dollars if you paid a database architect to build one for you. Amazing isn't it?

Data modelling is hard

One of the most difficult parts of building information systems, whether they are situational applications or regular applications is determining how to represent your real world scenario with a description the computer can understand. The concepts we have in our minds are complex and convoluted, but the computer doesn't understand when you describe something with all sorts of words like we did in our situational application brainstorm.

In this series of posts, I have simplified a lot of the concepts. I use People, Processes, and Things. Experienced data modelers use more advanced terminology like Unified Modelling Language (UML). In UML, the words are different than the words I'm using. Instead of People, they call them Actors, instead of Processes, they call them Use Cases and instead of Things, they call them classes. UML is much older than these blog posts and much more detailed.

Data Modelling is about Abstraction

When you are building your data model, you want to find the different things in your brainstorm that are alike. A recent example one of our users ran into was how to organize her cities, states, countries, and territories. She wants to describe a particular type of building based on which city it is in. There could be buildings in hundreds of different cities, so navigating this long list of potential locations could be difficult. Say you are adding a new building and you need to select the city it is in. Do you want to see a drop down box with 400 cities in it? Or do you want to select the country first and then the state and then the city? Might be a little easier right?

So there are two approaches to abstract this set of information. Your things are City, State, Country, Territory. What are all those things? How are they similar and how are they different? What properties do they have in common?

Well, cities, states, countries... they are different sizes. Different geographic locations on a map, based on longitude and latitude. Cities are inside states, which are inside Countries. In general, they are all Locations. They all have a name, Tulsa, Oklahoma, USA, North America. In this type of data model, you could say, "Countries contain states contain cities contain buildings." Uh oh... there's a lot more information about a building that doesn't really apply to a city though, right? A building has a number of floors, it's made of brick or wood or steel. It's a commercial building or a residential building. So we might not want to include buildings in the group of things we call "locations." If we did, then there would be a lot of cities with extraneous fields on the page when you go to add a new one.

So how do we represent these locations and buildings in our Situational Application? The first approach would be to create a City table, a State Table, and a Country table. Then, we create a building table and link it to the City table using what Qrimp calls a "Pick From." Pick froms let you pick an item from a big list of items. You want to pick one city for this building out of a list of 400 cities.

Other names for pick from could be foreign key (Relational Database), drop down list (Visual Studio), Value List (FileMaker), select box (HTML). They're all the same thing. They are represented on forms like this:

When you create a pickfrom in Qrimp, it'll build that form field for you automatically.

Another way we could describe this kind of information, this location information, would be to group them all together into one table, a locations table. A locations table would include a Name, maybe a description, and even a type of location: City, State, Country, or Territory. These things, these location types, then become their own table. When you add a new location, you choose the type of location from a select box as well. Your application may or may not be concerned about what type of location it is, or maybe it does.

If we use this single Locations Table to represent all of our locations, we get some neat functionality. We can represent it in a TreeView and drill down. Like this:

In that Tree View of Locations, if we click on New York City, on the right hand side in the related data section, also known as the right hand rail, you'll be able to add new locations or building by clicking the add links. Here's a screen shot:

Data Modelling is an Art and a Science

Data modeling is not an exact science, because have to make choices about how to build our data models. The Paradox of choice is that is makes us freer, but it paralyzes us. Don't let the number of options paralyze you, just do it. It doesn't have to be perfect. If you need help, email Qrimp Support, we'll help you decide. We've built hundreds, if not thousands of data models.

Data modeling is an art. Different expert database administrators will have different opinions about how to build a data model for different types of information. There are hundreds of data models for customer relationship management (CRM) systems alone.

There are pros and cons to different approaches. Some factors in these decisions include the kind of user interface you want to build on top of the data, performance issues and normalization, flexibility of the application, and other issues affect your data model.

Data Modeling takes a lot of experience and practice and you are going to make mistakes. Qrimp makes it easy to change your data model, so Qrimp is a great tool to experiment with. Qrimp builds most, if not all, of your User Interface automatically. Understanding how your data model will be presented will help you decide how to build the data model itself and this understanding takes time and practice.

In the next installment of this series, I'll show you how to take an abstracted description and actually build something to store that information in Qrimp. My suggestion to you is: Experiment. Build a simple model and then enter some data. Is it smooth? Is it easy? Does it make sense? Will it make sense to your users? If it doesn't, then build another model to represent the information in another way and see if that is more intuitive for you and the people who will be using your situational application with you.