Introduction

Per my introduction article, I’m learning the hard way – with the latest from Microsoft.  Yesterday I created a SQL schema from a Twitter archive which was “fun”, but didn’t provide me with a decent enough schema to model a complete end-to-end design for something meaningful.

So I’m back to square one, and I’m going to model a database schema using the latest tools for this purpose instead.

Creating a SQL Database Project in Visual Studio 2013 Preview

When I fired up Visual Studio 2013, I initially couldn’t find the SQL Database project.  Eventually I did, but rather than under the more intuitive “Other Project Types” it was instead listed under the less accurate “Other Languages”.  I checked the previous version (Visual Studio 2012) and it seems the prior version has a less ordered view of project types.

 
Visual Studio 2013 Preview / Visual Studio 2012

Starting with a blank project

Once you’ve created a SQL Database project, it’ll be empty.  The obvious first step is to add a table, and then start modelling from there.  In this article going to discuss my personal methodology and approach to modelling.

Adding a Table

Defining A Purpose

My intention is to build a data schema to house information relating to photos I’ve taken, with the purpose of having said photos rendered on a website (which I’ll build in a later article). 

The model needs to expand as the design does, but for now I need a starting point.  I’m taking a “data view” and will start by defining entities which I’m fairly confident I’ll need.  I’m going to work off a design based on “meta data” which accompanies a photo – what sizes it comes in, where it was taken and what sort of genre(s) it might belong to.

Let’s look at a basic schema definition:

• Catalog (a table containing core information about my inventory of photos)
• CatalogLocation (a join table)
• Location (a reference table, where the photo was shot)
• CatalogGenre (a join table)
• Genre (a reference table)
• CatalogSizes (a join table)
• Sizes (a reference table listing available sizes)

Each reference table has a “join” table to the main table (Catalog) which allows me to model a many-to-one relationship for each reference type.

This should be enough to get me started for now.  I’m going to add each of these tables as individual items to the Visual Studio project, with the following definitions (subject to change).

Building Table Definitions

Once you’ve added a new table, Visual Studio will bring up a design view in the centre screen.  I’ve docked the Properties window on the left hand side so that it is easier to interact with.  From here it’s a fairly straightforward job to simply punch in the field (column) names and types, observing that the T-SQL used to create the table is reflected in the code view.

Setting the Primary Key

By default, the Primary Key is the first field/column, but in a join table you might want that PK to span more than one field/column.  How do you achieve this?  Remove the PK definition and then select both target fields and right click:

Which automagically updates the T-SQL:

 

Setting an Identity Column

For some databases, this is called “autonumber”, but essentially what we’re saying is that a field/column which needs to generate a sequential ID, is given the “identity specification”.  We can set this using the properties windows as illustrated below:

Note that my general preference is to NOT  use identity specification for reference data tables, as it means that theoretically the reference data could then use different ID numbers between environments/versions etc.  My general preference is for reference data to use predictable static ID values at all times.

I am using a n identity seed for the PK of the Catalog table, all the other tables do not use an identity seed, they will need to be populated programmatically or by a post-deployment script.

Once the schema has all the basic elements, you can actually build and deploy the schema.  Consult the project settings dialog to set the target platform:

When you are ready to publish, right click on the solution and select “Publish”.  This brings up a new dialog which you can set the destination database instance:

I’m publishing to my local default instance.  All done.

Modelling the base Schema

Sorry, but I honestly find setting the advanced design work is far faster to do in SQL Management Studio.  You can set relationships, indexes and other settings from within Visual Studio as you have done with creating Tables, but it’s just too damned slow. 

By publishing my schema to SQL Server, I can now create a SQL diagram.  First, you’ll have to enable diagram support (right click on Database Diagrams and select New..”).  Add all the tables and you’ll get an unordered view of the schema.  Right click on the surface and select Zoom to zoom out (it makes it easier to arrange the tables).

The first thing I do now is change to a custom view for the non-join tables.  Right click on any table and select Table View->Modify Custom.  This allows you to select which properties are shown in the designer, when a table is set to show “Custom”:

Now, I can lay out the schema and then add the relationships.  Just click and hold on a PK and drag it to the target Foreign Key (FK), e.g. click and hold on the CatalogId column and drag it over the corresponding CatalogId on a join table.

When you are finished – don’t save!  I’m going to show you a nifty way to get these changes into the Visual Studio project.  In the top left hand toolbar there should be a button called “Generate Change Script”.  If there isn’t, right click on any toolbar and make sure “Database Diagram” is selected.

I’ve saved the resulting T-SQL to my desktop.  Now switch back to Visual Studio.  In the project, right click on the solution:

Select Import –> Script (*.sql) and select the change script you just generated.

I’ve set the folder structure to “None” since all my schema are in the root.  This should successfully merge related changes in to the base schema file:

Open up one of the join tables, and you can see the relationships.  Now for the fun part.  Go back to SQL Management Studio, close (don’t save) the SQL diagram and then delete the database.

Return to Visual Studio and redeploy your latest project.  Return to SQL Management Studio and you’ll (hopefully) see a new version of your database.  Create a new SQL Diagram and add all the tables.  You should be surprised to see all your relationships joining the base schema tables:

Repeatable Process

This is a repeatable process!  You can continue to model in SQL Management Studio and to import those changes into Visual Studio.  Just remember to export the SQL change scripts.  A word of warning: SSMS tracks all changes, so be careful what you do in a design window, or you may export changes you don’t intend!

Schema Compare

If you get caught up and forget to export the changes, there’s another option: Schema Compare.

This allows you to make whatever changes you like in SQL Server and simply have Visual Studio work out the changes for you.  From the project, right click and select “Schema Compare”:

Once opened, select your target schema, then click the compare button.  I’m adding two self joins to Location and Genre, and a new field/column to the Genre table: ParentGenreId.  Here’s how the schema compare detects the changes:

If you want to update Visual Studio’s schema, rather than the target, just click the “switcheroo” button:

Then you can update the Visual Studio project’s schema.  Check out the Genre table design and you’ll see the new column and constraint has been added:

Setting Reference Data

As you may have noticed from the above, we have no seed/identity column defined for the reference data columns.  This means that each reference data table will need to be “primed” with data before the table can be used.  Right click on the Scripts folder and select Add->Script:

You can select differing types of scripts to add.  I want a post-deployment script, so I’ve selected that type and named it accordingly.

At this point, I’m going to switch back to SQL Management Studio.  It’s easier to enter the data into the table and then generate the T-SQL.

Create Reference Data

Note that this is a repeatable process.  The direction here applies to a single reference data table, repeat as necessary.  Right click on any reference data table and select “Edit Top 200 rows”.  From here you can hand enter reference data as illustrated below:

When you are happy, close the editor and right click on the database.  Select Tasks->Generate Scripts.  Click Next on the first page.

You can pick just the reference data tables if you like, you just want to pick tables with data you’ve entered. Don’t rush through this, the next step isn’t intuitive.  On the next screen, click on the “Advanced” button:

On the property “Types of data to script” select “Data only”.  Proceed to finish the wizard:

Return back to Visual Studio, then open the file you generated from the wizard:

Just cut and paste this into the post-deployment script.  Then you can delete teh database and re-publish.  You should see the reference data sits in the table now.

Results in:

Summary

So this has been a fairly long article covering off what I would consider to be the absolute basic essentials in getting started with basic schema modelling.  I’ve covered off an approach to help you create database schema elements within a project in Visual Studio 2013 Preview, utilizing some of the functional built-in to make life easier.

What we’ve covered off here:

• Creating basic database schema (Tables)
• Setting Primary Keys, join table composite keys
• Publishing to SQL Server
• Using the SQL Designer to create relationships
• Using the schema compare utility to generate and apply changes
• Created reference data scripts for post-deployment
• Generated T-SQL scripts using SQL Server’s Generate Scripts wizard

Believe me when I say that this is just the tip of the iceberg!  We’re a long way from what I would consider to be a professional level of data modelling and design.  This was a very light example to introduce the concepts of generating change scripts and an introduction to the schema comparison tool.

The “next level” would include the following:

• Indexes and constraints
• Programmable artefacts (functions, stored procedures)
• Views
• Security (roles, logins)
• Employ different SQL Schemas (other than the default ‘dbo’)
• Full text indexes

…and more.

I’ll upload a complete T-SQL script and link from this article, which will create the full database and reference data, so you can continue this journey with me.  The next article will focus on the usage of the latest Entity Framework release, and show how we can access and manipulate data.

Check back for more later.