Game design: Database, do you use Access 2013 or SQL 2012 or Compact SQL or WHAT?!

You have a lot of choices for your database design.  You could use Access to front end a SQL or sharepoint site, doing this you optimize your security situation, but some people view Access as an out of date product.  Too bad for them, Access allows you to quickly build a database, have it documented and extensible quicker then any other database system that I know of.  Hard core folks don’t like it because they never worked with it. Oh well.  It is a new world.

Access 2013

To give Access 2013 download it from: https://aka.ms/qykyqw , some of my teammates view Access as trailing edge technology.  I am not sure and there are a lot of people using Access so I am going to keep up to date with it.  However in this example I will use the SQL Express 2012 form of database.  In a later blog I will show you how to use Access, generally it is extremely easy but Access is designed to be used with only a few users at the same time so you need to be careful about how you use it.

Plus there is the new lightweight distributable SQL Express database, which is cool, small and fast.  However, it is likely you will encounter Access in situations where you should be careful with statements like: “ Gee boss I just changed the database from Access to SQL, I didn’t see what difference it would make.” And you find out it was the way that your boss kept up to date and they DO NOT have time to learn a new system.  At that time it would definitely suck to be you.  Access by the way has some cool processes and works easily with SharePoint. 

Most importantly you might not have noticed the difficulty with updating the existing distribution systems.

Oh ok, in the 90s I made a decision like that and didn’t get my monthly contract renewed (sort of like being fired as a consultant).  It was ok in that I got a better paying job the same day, however, you don’t want to create pain for your customer.

Compact SQL:

Going to use SQL Express 2012 to discuss how to use the SQL Management tool.  This is a cool database with a high degree of security, and we will use it in the final project.  But not now.  Doesn’t mean that you should try it.

SQL Express 2008 R2

To use SQL Express 2008 R2 (and the impressively excellent download): https://aka.ms/romrp2 , make sure to include the management tools.

SQL Express 2012

https://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx , then select the SQL Server Management Studio Express

In case you have SQL Express 2008 R2 on your system, the management tools may not work as you expect.

Discussion

I would recommend the SQL Express 2012, which is what I am using for the game design series that I am working on.  Not a popular thing, but at least you are reading it, thank you very much.  The web download requires that you wait for the applet to get the download.  I am using the big version with tools, etc.

For example I needed a quick database for my game design.  I could write SQL statements using the command level, or I could connect to a SQL Express 2008 R2 database.

Let’s say you are starting from scratch and you know that you need a pretty standard database to get started with your game.  What you might say, I need a database? Really, that’s no fun.  Right, but knowing about databases can get you work that pays some bucks.

So let’s do the data diagram using the Access 2013 tool, create an Access database (later you will connect it to a SQL Express 2008 R2 or a Compact SQL database using ODBC), do this by selecting the Database Tool and then Relationship, here you can add tables, etc.  In a later blog we will explore the more difficult process of using the command line with a SQL database. 

You could use the command line, but you need to make sure to install the Management Tools or the SQL 2008 R2 Server with Tools, which is a pain when you encounter this for the first time, but if you use SQL Express in everyday work, you may use other tools to manage it. 

Make sure you test the installation using the Basic Manager.

What if I have SQL 2008 R2 on my system?

It should work, but the SQL 2008 R2 Management Studio attempts to launch and didn’t connect for me.  Once I removed SQL 2008 R2 completely, including the SQL Setup files, the SQL 2012 management tool worked fine for me.  Your mileage may vary, as the saying goes.

Once you get the SQL 2012 Management tool to show up

It looks just like Visual Studio and uses all of the same metaphors.  In general I haven’t been using the Management Studio for the past couple of cycles, rather using Compact or XML files generally.  It is nice that SQL 2012 Express works seamlessly.  Nice.  Although I didn’t have any problems with SQL 2008 till I had the interaction issues with SQL 2012 and this might have just been me.

Now let’s use the Management Tool

The management tool launches with the following look,

These are system databases, at this point you should consider the following promise to yourself: “I will never touch the System Databases in production”.

So let’s add a Database that we can use to play around with:

Now add a table so we can add stuff to it.  This all makes sense over the long time I take to tell the technology stories:

Add a table, partial screen shown, a column called name is added with the data type of text, add two more columns after this image:

Add these columns, then save the table, I saved it with the name of UserNames, for no good reason.

Now let’s add some data, it’s simple, but there is a confusing factor, the Management Studio will write the script for you, use the following chain to get the Insert script:

Here is what the autogenerated code looks like, you have to make some simple changes:

Between Values and Go,  if you used the same column names as I did, you can copy and paste the following to paste these three lines of data:

('Silly Billy', 10, 100),
('Joey HillBilly', 20, 200),
('Mary Berry', 30, 300)

Click the !Execute button on the toolbar:

Your screen should look like the following after you click the Execute button:

How did this compare to using the Command line?

In my next blog I will show you how to use the command line.  Which one to use?  It really your choice.