Good afternoon everyone!

Today we will be discussing the importance of database design and how thinking outside the box from the offset can prevent any major database changes in the long run. Some database changes are inevitable as a system grows and may be fairly easy to implement, however, changing names of columns, names of tables and adding new relationships that interact with pre-existing ones can quickly become a complete nightmare to modify. This is because the objects that you are changing could be referenced all over the place, including within Triggers, Stored Procedures, Views and even in the back-end C#/ASP.NET code. All references would need to be re-pointed to the new names, otherwise Server Errors will be appearing all over the place and could render a system completely broken.

Steps to Creating the Ideal Database Solution 

It may be useful to begin with creating use cases and/or flowcharts in order to work out how customers will interact with the system. Usability of a piece of software is paramount to its success as, if it works but it's not explicitly clear on how an end user can use it, it will be as effective as a solution that doesn't work at all. You should use this opportunity to think of possible on-screen messages that may have to appear, when validation may be required for certain page elements such as username and password entry etc.

The image below shows an example of a flowchart that I have created for a booking system whereby a user can reserve a table at a restaurant:

If the system will have pre-existing data that comes from either a spreadsheet or a redundant system it may be a good idea to start looking at this information to help determine what database tables and fields you will need. If you can already split the data within the spreadsheet into columns then that's fantastic and really helps.

Whenever I design a database, I always hand draw what I think the final design could look like. It doesn't have to be neat at all, just providing it makes sense to you when you come to putting it in a computerised format then it is fine. I also use this opportunity to decide on table names and primary/foreign key fields, which may be easier to do after analysing pre-existing data to determine exactly what attributes I will need. Ensure that you always choose appropriate naming conventions! My blog post containing Tips on How To (Correctly!) Define Your Tables may help you. Here is a quick example of my database drawing:

Next, I am going to transfer my ideas into an Entity Relationship Diagram in Visio. This is where I start to think more about what columns I will need in each table and their data types. As mentioned before in a previous post, it's very important to have one naming convention and to stick to it, plus it's important to use sensible naming conventions that shouldn't require changing in the long run. It would also be a good idea to normalise your database design at this level before it has been created properly in SQL Server (For more on normalisation, read my blog post here):

Now, I can transfer my Visio design into SQL Server. You could create the tables either using SQL code or by using the designer (you should have an idea on how to do both) but for the purposes of this exercise, I have decided to create the database using the code below:

As a database diagram, it would look like this:

And there you have it! The basic design principles in order to create a database. As you can see it isn't too dissimilar from the original designs but that is probably because there wasn't much to the system. The larger the system, the more opportunity there is for errors to sneak in unexpectedly. However if you follow these simple steps, you should be able to create a pretty good database design on your first attempt.

As a Final Word... 

There may, unfortunately, be occasions where you have no choice and have to rename tables and columns. If this is the case, a useful tool called ApexSQL Refactor 2013 (discovered by one of the Senior Developers at Light Speed IT Solutions) may be of use. Follow the link below for more information and to download:

http://www.apexsql.com/sql_tools_refactor.aspx

If anyone has any questions, comments or feedback on any aspect of my blog please don't hesitate to contact me. Thanks for reading!

What is a role? 

A role is a set of behaviours, conditions and rights that can be applied to an actor within a particular situation. Roles can be applied to many different situations in society, however this mini report will focus on how roles can been implemented within a piece of software to make it more secure.

What is the purpose of roles?

Roles are controls that are assigned to an application. When roles have been assigned, it means that there is some protected data that can only be accessed via log in (username and password). Typical roles control:

• Who can log into an application in the first place. 
• What data the user can have access to as read-only. 
• What data the user can update themselves; add, edit or delete. 

All companies want to use various user roles that allow different modes of access. I am going to use a diagram to illustrate the hierarchy (see below). Generally, the higher up the hierarchy, the more permissions and rights that user has.

• Users – These are read-only access roles that allow access to their own account log in details, give the ability to modify their own details (e.g. change their name, update their password), view their own projects and see the shares and share holder information linked to both of those projects. 

• Managers – These are the directors who are in control of their own projects. They can only view the projects that they are directly assigned to (they would have no need to view the information of other directors that are unrelated to their own work) however, they have complete access and the ability to modify any information within their own projects. They can also view the other members within their own project but do not have the permissions to make adjustments to existing user accounts. 

• Administrator (or super administrator) – These accounts are used very sparsely as they have no restricted access at all and can view all data and information from all projects. All data can be modified or deleted and new data can also be input anywhere in the system. All users and user information can be accessed too which allows, for example, if a user has forgotten their password for the password to be changed. If users are not applicable in the system anymore, they can also be removed. 

There is also the possibility of having anonymous access roles that allow complete strangers to have access to the site. Some companies would want this but some would not.

It is also possible to add permissions to buttons and controls using validation rules, which not only stop users who should not have access to them from using them, but will hide the button completely from the screen so the user that has logged on would not even know that there was a button there.

What would happen if roles did not exist?

If roles did not exist, then data would certainly leak out to the wrong people. Roles are a safety barrier that prevents confidential and private information from being accessed by people who should not have access to it. It is incredibly important to ensure that you assign the correct roles to the correct people, otherwise the objective of roles has been defeated, and they are there to protect data!

Designers and developers create Use Case diagrams in order to help decide what user roles an application will adopt and what permissions they will have. The diagram below shows a quick example on what a typical Use Case diagram looks like:

So how would I set up a user role tables within my database?

It is very easy and straight forward to set up user roles within your database to be used within your asp.net applications.

Firstly, create 2 tables called UserAccount and UserRole:

There is a reason why I stated use those names. In SQL coding, try to avoid using names such as user, role and password: 

This is because, as you can see from the screen grab above, they show up blue as they are reserved SQL keywords. Try to avoid using these as it can get SQL very confused. You could possibly use them by places square brackets around them:

But in this example I am going to use different names.

Next, you will need to decide on the relationship between users and their roles. Can 1 user have multiple roles? Possibly. Can 1 role have multiple users assigned to it? Most probably. For the purpose of this tutorial, I will suggest creating a bridge table:

And that really is it! Just remember to encrypt your passwords to keep them secure!

In your asp.net application, you will be able to configure your settings so that it follows the security in your database. However, it is also possible to use the standard asp.net membership system which can generate users and roles automatically. This method is just as good (it contains a similar table structure with a bit more), the only difference is particular code generators such as Iron Speed Designer may find it easier to interpret your own created tables when setting up security.

For the sake of these tutorials, I will be using SQL Server Management Studio on a SQL Server 2012 Server.

The first lesson will be to create Databases, Tables and Fields using SQL code.

Create a Database 

It is possible to create Databases using the user interface and then adding a database name. However, you can quite easily select "New Query" from the toolbar and type in the following:

On clicking "Run", the database will be created and displayed within the Object Explorer panel (commonly found on the left hand side of SQL Server Management Studio)

Creating Tables and Fields 

Next, we will want to create some tables to go inside our newly created database. Remember before we do this, always ensure that you put "USE databaseName" above the CREATE statements so that SQL Server Management Studio knows where to put the new tables (see code below): 

Next , create a table called Customer:

In order to identify between the records, a primary key column will be required. This table will typically store customer details, therefore customer FirstName, LastName, Address, Postcode, ContactNo and EmailAddress would be good places to start when thinking of potential field names. It is also a good idea to think about what data types these fields would take. For a unique key, either an integer (int) or a globally unique identifier (guid) would be ideal. For this tutorial - I suggest using an integer despite it being possible to use both. There will be a later blog post to explain the differences between them so you can decide which best suits your situation.

Now you will need to consider how much space will need to be allocated to each field. For example, a FirstName shouldn't really contain more than 35 characters. Therefore, 35 will be placed in brackets next to the data type in order to signify this. Note that some data types may not allow you to specify an amount of space (such as integer values) as they already have a set amount of space to use. It will also allow you to set the size as "MAX" in the possibility that -- In the case of primary keys, they will need to be set as Identity fields so that they automatically generate a an ID number. You will notice in the code sample below that it is made up similarly to a co-ordinate (1,1). The first number declares the seed (the number that the Primary Key will start at) and the second number declares the increment (the amount in between each primary key as it's inserted into the table). By default, fields are set to allow NULLs if no data has been entered by the user. You would not want this for all of your fields so be careful and think about what fields you want as null able. To declare a field as NOT NULL, all you have to do is place the NOT NULL at the end of the declaration. Note that Primary Keys must always be NOT NULL. 

The code below creates the basic field types and declares the CustomerId as a Primary Key:

And that code will create your first database table! Now, create an Item table in exactly the same way (see code below):

Creating Relationships

We will need to consider relationships between these two tables. You can have different relationships between tables as depicted in the table below:

So looking at our scenario, can a customer buy many items? Yes... Could many customers buy the same item?... Yes! Therefore, this will require a many to many relationship.

In order to create many to many relationship tables in SQL Server Management Studio, a bridge table will need to be made in order to link the 2 by their ID's. This will normally consist of its own unique Primary Key, along with the 2 Primary Key fields from the 2 tables you have just created. These will be declared as Foreign Keys as they do already exist, we are just pulling the values from the other tables. Don't forget to reference the foreign keys so you know the exact table and exact field they are coming from. See the example code below:

Let's take a look at what we've done! 

In order to see what has been created, click onto "Diagrams" and create a database diagram adding the 3 newly created tables into it. Note at this point that you may need to refresh the popup in order for it to display your tables!

Once created, it should look like this:

Seeing as this is an introduction, I am going to keep this post short and to the point (to help beginners who may be reading this blog).

What is a Database?

Let's start right from the very beginning. A database is a container for organised data that is easy to access, easy to store, easy to update and easy to manage. Please note that a database IS NOT a spreadsheet (and the reasons why this is will be discussed in later blog posts).

What is a Table?

A table simply contains data that is made up of columns and rows. Columns are vertical and rows are horizontal.

What is a Field? 

A field is another name for a column within a table. Tables can be made up with many fields with different data types. For example, a field that has an "int" datatype can only store an integer (or number) value between -2,147,483,648 through to 2,147,483,647 for a signed integer and from 0 to 4294967295 for an unsigned integer. No other characters will be allowed and an error would appear if an alternative input was entered.

What is a Record?

A record is another name for a row within a table.

What is DBMS?

A Database Management System (or a DBMS) is a software package that allows a user to create and maintain a database.

What is RDBMS?

A Relational Database Management System (or a RDBMS) is a software package that not only allows you to create and maintain tables of data, but also allows you to manage the relationships between tables.

So what is SQL? 

Structured Query Language (or SQL) is the standard language used to communicate with a database in order to SELECT, INSERT, UPDATE, and DELETE data. Further down the line, example code will be posted to demonstrate how these statements can be executed in order to manipulate result sets.