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

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

  • Comments 4

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: http://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): http://aka.ms/romrp2 , make sure to include the management tools.

SQL Express 2012

http://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,

image

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:

image

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:

image

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:

image

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

image

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:

image

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

image

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:

image

 

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

image

 

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.

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • Remember that you could also use the new version of Lightswitch to do your development. In doing this you would use the lightswitch designer to set up the entities and security... then publish the backend, using a suported database engine. Once published you game will be able to use the published Odata endpoints 9Including security), which would also mean that you could use the same system across different platforms.

  • Hey Glenn,

    Yes, Lightswitch, good idea, I am going to check it out.  My initial thoughts about Access kind of sucked.

    Hey, why not include a little advert for your consulting firm in your comments.

    Oh I can do that:

    Glenn Wilson is owner of Virtual Realm Consulting and you can find out more about his company at:

    www.virtualrealm.com.au/mykre

    Thanks for the feedback Glenn, it was a useful idea!

    Sam

  • Do you know ACCESS 2013 uses SQL azure as a backend for all web database apps?

  • Sweet thank you for pointing that out!

Page 1 of 1 (4 items)
Generic Content

Legal Note:

  1. The author, Sam Stokes, is an employee of Microsoft
  2. There is no guarantee that anything I write is correct, I do try to make sure that what I write is correct.
  3. Use anything written in this blog at your own risk
  4. Test what I write about before using the information
  • Sometimes posts will disappear because I realized that they really don't fit with the total chaos of what I write about
  • Links are tested and available on date of publication, what others do with their links are out of my control

Restrictions:

  • Whatever I write on this blog is unedited by management or anyone, which should be obvious from the randomness of the blog.  I simply write about things I like.
  • Microsoft does not restrict me in anyway, so I generallyblather about whatever I want to.
  • For personal reasons I will not write about:
    • Company confidential stuff, mainly because Microsoft is more transparent than any company I have ever worked for.
    • Other people unless they give me permission to write about them