DD&D – Database Design & Development

With reference to a previous post, we will talk about designing what can essentially be described as the foundations of our building, the Data Trier (more specifically the Database).

Planning

“You get it wrong here, all sorts of problems will bubble up to other layers of your application!”

As stated before the key to a good application is planing, literally any planing is better than just diving into a project head first.

Think of a building, and imagine that we start work on building it, the whole structure is going to rely on a solid foundation that in turn will handle all the stresses of the structure above and absorb any changes or shocks, the data tier (or database) is the same thing.

The first and foremost thing is to identify the actors and objects in your application structure and decide what is going to be in your database. Not all things have to be drawn from the database, this can be the file system or even web services. This all depends on what you are storing and how often you will need to access it.

For example, say we are creating an application that will list out all objects offered by a “Free-to-play” games provider such as Ad2Games or Ad4Game? The games are retrieved via a web service and are usually in JSON or XML format

{
   "GameName":"SomeGame",
   "Description":"Some description will go here",
   "Categories":[
      {
         "Name":"Category1"
      },
      {
         "Name":"Category2"
      },
      {
         "Name":"Category3"
      }
   ]
}

We can of-course draw this info from the web service and simply display it. But what if we need to do some sorting or searching, which the API doesn’t support?

We should, of-course, transfer this data to the database, reduce the API calls, increase site performance and transfer this to the database.

Design

In a typical relational database (the only type I will cover here) data is stored in organizational units called tables. These tables can have links in the form of foreign keys and unique identifiers in the form of primary keys. More can be read here.

Accessing the data

What do we do when we need to get a hold of our newly created data? We write queries! Queries are, simply said, commands we give to the database engine that should (but don’t always have to) retrieve results for us.

There are generally four different types of operations we execute to manipulate data, we use the “CRUD” acronym:

1. Insert (Create)
2. Select (Read)
3. Update (Update)
4. Delete (Delete)

So what is the best way to execute these commands from our application?

Being big headed, I will say that the best way is always through Stored Procedures. But in reality everyone has their own way.

Because we use the “Three Tier Architecture” model, we always prefer to segregate the data and application tier logically and that means separating the way data is retrieved back to our application. In the application all we really need is a mechanism to execute and possibly supply different arguments to it, that’s it!

Just as we supply arguments to a web service in the form of POST or GET variables, or even HEADER variables, we supply arguments to stored procedures in the form of the parameters they require. At the end, what the server does and how it does it does not concern the business logic layer. The only thing that is important is the DATA CONTRACT (the data set that is returned).

Each project is unique, but adhering to simple guidelines such as this we are able to make our application both modular and scalable.