The ClickBank Marketplace - Random musings

Introduction to Software Development

In my experience of being a professional developer over the last twenty years I have identified that there are many ways to develop enterprise level software quickly whilst still providing the necessary stability and security that good software requires.

The purpose of this blog post is to provide a way for anyone wishing to become a web developer to quickly learn these insights whilst not needing to concern themselves with extraneous knowledge and principles that while applicable on paper are not needed in the real world.


Software requirements

I work with Microsoft products or what is known in the industry as the Microsoft Stack - The term term Microsoft Stack means that all software used to power a website is commercially made available from Microsoft.

Therefore in order to follow along with this post you will need to have a version of Microsoft Windows installed on your computer. We recommend that you install Windows 7 professional and have full administrator privileges so you can install and use the software we recommend throughout this post.


The architecture of a web application

A properly built application should adhere to what is commonly known as a three-tier architecture. Basically three tier architecture separates out an application into three distinct areas:

  1. Database or data storage area
  2. Business logic or the “Smarts of the application”
  3. Presentation layer commonly known as the Graphic User Interface or GUI

The good thing about having an application developed which adheres to the separation of the three areas above is that (for the most part) it is much easier to maintain.

For example, if you want to change the look and feel of your application then you will only need to change the presentation layer - the Business logic layer and database layers can stay the same.

Perhaps you want to provide some of your “application smarts” to a friend? No problem, in that instance you can expose functionality from the Business logic layer.

Feeling radical and want to completely change the database that “powers” your application? Again, no problem, you can change the database without having to worry about the Business logic or Presentation layer.

So, without further ado let’s dive right into creating the database that will be used to store information pertaining to your shiney new web application.


The database or storage area

Think of the database as the area where all of your data is held.

Database software is a special piece of software which specialises in the storing and retrieval of  information and can handle many people accessing the information all at once.

The universal language that is used to interact with Database software is called Transact SQL commonly shortened to SQL (Pronounced SEQUEL) and consists of four main commands:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

However, before we dive on into the intricacies of Transact SQL let’s install some database software.

There are many different types of Database software but generally the four most common ones installed on servers around the world are:

  • Oracle
  • MySQL
  • Microsoft SQL
  • PostGres

The cost of Database software varies wildly with an enterprise version of Oracle and Microsoft costing in the tens of thousands of dollars whilst some versions of MySQL and PostGres are actually free.

The good news is that some companies like Microsoft provide a stripped down version of Microsoft SQL for free. These free versions are known as “express versions” and are more than enough to power a small e-commerce website or web application.

If and when you outgrow the express version it is extremely easy to update to the enterprise version by simply restoring your database to a server that is running an enterprise version of Microsoft SQL. Finally, many hosting companies offer an enterprise version of Microsoft SQL as part of their monthly hosting package for less than twenty dollars a month.

So, where to get this elusive Microsoft SQL Express and how do I install it?

Downloading and installing Microsoft SQL Express

In order to download Microsoft SQL Express the easiest way to find and download the software is by opening a web-browser and navigating to Microsoft Download Centre

https://www.microsoft.com/en-us/download/

Once on the Microsoft Download Center homepage simply search for the term:

Microsoft SQL Express 2012

From the search result list look for a result that is similar to the following

Click on the link and you will be taken to the actual download page where you are able to see more information pertaining to the download

The main aspect to consider is the System Requirements needed to install the software; these requirements are able to be viewed by clicking on the “System Requirements” link.

Once you have confirmed that your computer meets the necessary requirements choose our language from the list of options and click on the “Download” button

The final step to download Microsoft SQL Express is to choose the correct version from the list of available options. At first glance the list of downloads can be unnerving for a first time user but essentially there are only two downloads we wish to concern ourselves with and of these two, we only need to download one.

So, if you are using a 64bit computer then you are able to download the version below:

Alternatively, (and if you are unsure what file to download) then we recommend that you download the file below:

Note: The ENU in the filename indicates that this is the English version of the software that is being downloaded - if you have chosen a language other than English then the filename may be different from the filename shown above.

Installing Microsoft SQL Express

Once downloaded double click on the file to start the installation and follow the prompts


If your computer has User Access Control enabled you may see the following prompt asking if you would like to allow changes to be made to your computer - in order to continue click on the “Yes” button.

In order to complete the installation of Microsoft SQL Express you will need to complete the following steps.


Step 1. Launch the installer and choose the very first option to install a stand-alone version of Microsoft SQL Express.


Step 2.

Accept the end user license agreement and press the “next”button. You are able to specify at this stage what features you would like to install and where you would like to install the software.

We recommend that you leave the default settings but please ensure that you do choose all of the settings shown below - Note: If there is no option for “Full-Text and Semantic Extractions for Search” or “Reporting Services - Native” do not be concerned as these are optional extras which are outside the scope of this post. Click on the “Next” button to continue.


Step 3.

Here is where we actually configure your new instance of Microsoft SQL Server. Since this is the first time that you will have installed Microsoft SQL Server on your computer leave the default settings (Ensure that “Default instance” is checked as per the screen below) and click on the “Next” button to continue the installation.


Step 4.

Continuing on with configuring our new version of Microsoft SQL Server we need to specify the way Microsoft Windows interacts with our new database instance. We can just keep the default settings here with the only exception being if you are not in the United States. You may wish to change the default collation settings if you are outside of the United States but it is not imperative.

Click on the “Next” button to continue.


Step 5.

We now need to configure security access for a administrator user who will be able to interact with the Microsoft SQL Server database.

Click on the Server Configuration tab and ensure that “Mixed Mode (SQL Server authentication and Windows authentication) is selected (As shown in the screen below).

In the text areas provided type in a new password (Use a password similar to the complexity that you you would use for your online banking). Since this password is the administration password it may be a good idea to write the password down in case you forget it in the future.

You can leave the remaining tabs and fields with their default settings and click on the “Next” button to continue the installation.


Step 6.

During the last step of the installation configuration process you have the option of choosing if you would like to send any error reports that occur during installation to Microsoft. Choosing to do so will help Microsoft to improve future releases of Microsoft SQL server. To begin installation click on the “Next” button to continue.


It is at this point that Microsoft SQL Server will be installed onto your computer. The installation process may take long time to complete depending on the specification of your computer.


When the installation has finished a report screen will be displayed showing any issues that occurred during the installation process. If you have all green ticks (Like in the screen below) you can close the installation window and be happy that you now have a working version of Microsoft SQL Server 2012.


Installing Microsoft Visual Studio 2015 community edition

Microsoft recently announced via their Community Connect program that there will be a full featured version of Visual Studio made available for FREE!

The good news is that there are no restrictions in regards to it’s use as long as you are developing in a team of less than five users - that’s perfect for us! So, before downloading and installing Visual Studio you will need to ensure that your computer meets the following system requirements:


Operating System:

Windows 7 (With service pack 1) or Windows 8 or 8.1

Hardware Requirements:

1.6 GHz or faster processor

1 GB of RAM

20 GB of free hard disk space

5400 RPM hard disk drive

DirectX 9-Capable Video card with 1024x768 of minimum resolution

So, if your computer meets the above system requirements, let’s get started:

In order to download the Visual Studio Community 2013 edition you will first need to download the installer which is available via the following website:

https://www.visualstudio.com/en-us/products/visual-studio-community-vs

To actually download the installer click on the green download button as shown below

Once the installer has downloaded click on the installer and click on the run button if you see a prompt similar to the screenshot below - there’s nothing to worry about it’s just Windows asking if you really want to install Visual Studio.

The following screen will prompt you to specify the location where you would like to install Visual Studio on your computer, we can leave the installation location as it is but ensure that you do change the type of installation from Typical to Custom as shown in the screenshot below:

Click the Next button and ensure that the installation screen is the same as the one below - we are only interested in installing the Web Developer Tools

Again click on the Next button to start downloading the files necessary to complete the installation

You may receive another “Are you sure” prompt as shown below - it’s all ok, just click the Yes button to continue

The files necessary for installation will now start to download as shown below - depending on your Internet connection speed this may take quite a while so sit back and relax and read the rest of this post

Step 1

Click on the following URL [Visual Studio Community 2013] and you will be shown the following screen.

An introduction to database programming with SQL

So what exactly is SQL?

The letters SQL are an acronym for Structured Query Language that is a programming language which lets you access and manipulate relational databases

What can we use SQL for?

We can use SQL can retrieve data from a database

We can use SQL to insert records into a database

We can use SQL to update records in a database

We can use SQL to remove records from a database

SQL can also be used to create new databases, new tables inside the database, view permissions, create stored procedures, create views and other wonderful things.

However for the purposed of this post we will be concentrating on the Create, Retrieve, Update and Delete functionality (Commonly referred in the industry as CRUD) of SQL.

Before we can start investigating the intricacies of SQL we need to build a database with all the relevant tables.

For the purpose of this post we will be creating a Web application that we can use to keep track of our favourite books.

Planning the software

When creating software you need to decide on what functionality you would like the software to perform - in the industry this work is called a functional specification and is usually performed by a Business Analyst or Analyst / Programmer.

Creating a full blown functional specification for our demonstration piece of software is certainly overkill and beyond the scope of this post but we can certainly decide what we think the main functionality will be.

To start with, write down what what you believe the software should do and anything that may interact with it; for our post application we could write.

I have many books and I have an extremely hard time keeping track of them. Sometimes one of my friends borrows a book and I forget that they have it so I have to phone them asking if they still have it (which is embarrassing!)

I want to be able to know what books are in my bookshelf and also what books have been lent to friends.

What would also be neat is if I could search my book collection by keyword to see what books I own on a specific topic.

What we are doing by writing the above sentence is determining the functionality that we would like our software to perform and is the beginning of what is known in the industry as a “Use-case analysis”

The next step is to break down our sentence into parts that we can model in our software - lets take a look

I have many books

A book is an object e.g. It has distinguishing attributes like a title and author - we can model that in our software so our first object will be a book

I have an extremely hard time keeping track of them

That sounds like something we need to do e.g. keeping track sounds like some functionality the software will perform, let’s make a note of that

One of my friends

A friend also has distinguishing attributes like their name or age - we can model a friend as well. It’s probably better to call the model a person instead of a friend :)

Borrows a book

Again, this sounds like something that someone does - they perform an action - they “borrow” a book. Our software will need to perform some sort of “borrow” functionality so we best keep track of that as well.

So I have to phone them

This is interesting, it’s an action e.g. A person phones another but do we really want our software to perform a “Phone” action? Not really, remember a person phones a friend and not the software. In this instance we are probably better off making a note that a friend has a phone number, in the industry these are called attributes so we could say that a friend has a “phone number” attribute; we will use that later.

Continuing with the next part of the sentence

I want to be able to know what books are in my bookshelf and also what books have been lent to friends.

We have already determined that a book is something that we will model but there is an action here:

I want to be able to know what books are in my bookshelf

This is definitely something that we want the software to perform - it sounds like we need to offer a way for us to search books

also what books have been lent to friends.

This sounds like we need to determine if a book is either still in the book shelf or has been lent to a friend. Remember previously when we talked about possible attributes of a person including their name and phone number? Well books can have attributes as well, the most common would be book title and book author. To determine if a book has been lent out to a person we could assign an attribute to a book called IsOnLoan which would determine if a book is out on loan to a friend - let’s do that.

The final part of our specification is

What would also be neat is if I could search my book collection by keyword to see what books I own on a specific topic.

if I could search my book collection by keyword

The above sentence fragment contains both an action we want the software to perform and an attribute that can be assigned to the book.

The action is Search and the attribute is Keyword

Now that we have gone through our sentence and abstracted out the models that we think we will need as well as the actions that our software will need to perform we can put it all together.

The first model we found when going through the sentence was a book - let’s make a note of that:

Model name

Book

We also determined that a book can have attributes associated with it such as Title, Author so we will make a note of that

Book Attributes

Title

Author

There are some other attributes that can be associated with a book as well, we will add these in too

Published date

ISBN number

We also determined while going through our initial specification that a book can be on loan to a friend and we decided to create a special attribute called IsOnLoan which determines if the book is in the bookshelf or out on loan to a friend.

So, our final book model with its associated attributes looks like the following:

Model name

Book

Book Attributes

Title

Author

Published date

ISBN number

IsOnLoan

What about the other model we discovered while doing our analysis? The person model - let’s examine the attributes of the person model

Model name

Person

Person Attributes  What are common attributes a person may have - How about a name?

First Name

Last Name

We also need to be able to phone the person so let’s add a phone number as an attribute

Phone number

In summary, our person model now looks like the following

Model name

Person

Person attributes

First Name

Last Name

Phone number

Actions

When we were going through the above sentence we discovered some actions that we would like the software to perform - notably:

  • A person “Borrows” a book
  • “Keep track” of  books
  • “Search” book

From the above main bullet points we have discovered that the software will need to perform tje following main actions:

  • Create an association between a person and a book e.g. A person “Borrows” a book
  • “Keep track” or provide the ability to see if a book is in the bookshelf or on loan to a person
  • A “Search” facility so we can easily identify if we own any books on a specific topic

Tip - A nice easy way to determine what should be a model and what is an action is to apply the following rule of thumb:

  • Any nouns in the sentence can probably be modeled
  • Any verbs in the sentence may be an action that the software needs to perform

 

Creating our first database

A database is a way to store information that our software will be using on a daily basis. The good thing about using a database to store information is that our data will be saved even when our application is restarted or the server is taken offline.

As previously mentioned one of the key components that make up a database is what is called a Database “Table”.

A “Table” can be based on a model and contains attributes that are associated with that specific model.

So, let’s get our hands dirty and create a database table based on our Person model with the attributes that we identified earlier.

Creating the database

In order to create our shiny new database we will need to use a special piece of software known as “Management Studio” - fortunately Management Studio is easy to find

Once you have located and clicked on the “SQL Server Management Studio” link and management studio has opened successfully we will need to do some house keeping

To create a new database using “SQL Server Management Studio” right click on the very top node named “Databases” and a new window similar to the one below should appear.


Using your mouse select the “New database” option from the list of available options and a screen similar to the one below should appear.

As you can see in the above image I have named my database “MyBookShelf” so for the purposes of this walkthrough I suggest you do the same.


Click on the “Ok” button and your database will be created as shown in the image below

Creating the database tables

To create a new database table based on our Person model we first need to expand the features located under our new “MyBookShelf” database; we can do that by clicking on the little [+] icon located to the left of the “MyBookShelf” database.


Once the list of features have become expanded right click on the “Tables” icon and a new menu should appear similar to the one shown below

Using your mouse select the “Table” option (Sometimes you may have a “New Table” instead of just “Table” - it’s all good, they perform the same purpose) and a screen similar to the one will appear.


An introduction to SQL datatypes

Before we start creating our new table based on our model we need to learn and understand the basic SQL datatypes.

So, first of all let’s understand what is the definition of the word “datatype” - Wikipedia defines a datatype as:

In computer science and computer programming, a data type or simply type is a classification identifying one of various types of data, such as real, integer or Boolean, that determines the possible values for that type; the operations that can be done on values of that type; the meaning of the data; and the way values of that type can be stored.

What does the above mean in simple terms?

Essentially whenever information is saved into a database it needs to be saved as certain “Type” - for example, if we want to save a person's age and we say that the person's age is 38 then we need to use the Microsoft SQL Type int. If we want to save a person's date of birth then we need to use the Microsoft SQL Type Date.

Below are the main Microsoft data types that we will be using for the example in this post:

DataType: int

An int can be used to store a whole number in the database - an example of a whole number is 1, 100, 56334, 127634. There is a maximum value that an int datatype can store in the database and that number is 2,147,483,647. If you need a larger number than that then you can use the bigint datatype which can store a maximum number of 9,223,372,036,854,775,807(!)

DateType: date

A date datatype can be used to store a date value in the database - an example of a date value is 01-Jan-2015 or 24-dec-2011 or 21-feb-1911. In order for a date value to be stored in the database the value must be a valid date - for example, if we tried to store the date 31-feb-2015 then we would receive an error as there has never been a 31st of February (and probably never will be!).

DataType: nvarchar

A nvarchar datatype stands for numeric varchar and can be used to store a set of characters in the database such as a person's name or the name of a city. An nvarchar can store numbers, letters, exclamation marks, a full stop - essentially anything on your keyboard can be stored in the nvarchar datatype.

There are many other Microsoft SQL data types available but these are outside the scope of this post. A more thorough overview of all the Microsoft SQL data types is available via the link below:

https://msdn.microsoft.com/en-au/library/ms187752.aspx

Now that we understand the basic Microsoft SQL data types let’s continue creating our database table for our person model.

The first attribute that we need to create when making our database table is known as the ID attribute. The ID attribute is a unique number which is associated with a specific record in a database table. Think of it in the same context in the way that a social security number is associated with a person and is unique for the person.


Fortunately it is extremely easy to create a unique identifier within Microsoft SQL. When you create a new table you simply Enter ID under the Column Name heading, set the data type as int and uncheck the Allow Nulls checkbox as shown below.

To expand further we can say that we have create a new Column called ID, it will hold whole numbers such as 1,2,10, 344, 9000 etc. Finally by unchecking the Allow Nulls column we are saying that this column must always contain a value.

The next aspect we need to understand about the ID attribute is that each time a record is added to the database (e.g. Each time we add a new book to our collection) we need to automatically assign a new ID to the book so we can reference the book later via this ID.


Once again this is easily done via the Column Properties which appears at the bottom of the page.

The specific property we are interested in is displayed under the heading “Identity Specification”

All we need to do to get the database to automatically create a new identification number for each record is to change the Identify Specification from No to Yes.

Now, whenever a new record is added to the database a new unique number will be associated with the record - we use this number in the future to identify individual records in the database.

Now that we have way of identifying records in the database we can carry on and add in the other attributes of the person model - Firstname, LastName and PhoneNumber

All things going well your table design should look exactly the same as the screen above. Before moving on let’s discuss each of the new columns in the table above.

FirstName - This column will be used to store the First Name of the person. We have specified that the DataType is of type nvarchar(5) and it does not “Allow Nulls”. So, what does this sentence actually mean in plain speak?

We have created a new column called FirstName and it will accept any combination of characters or numbers up to a length of 50 characters (If we try to put a name longer than fifty characters then we will receive an error message). Also, we must provide some sort of information for a FirstName.

Coming soon - an introduction to Primary Keys and their importance :)