Google+

When should you use database constraints?

A discussion came up at work recently about the extent of constraint usage in your databases. There were basically 2 camps:

  1. Constrain everything humanly possible. If it's an integer that wouldn't normally be negative, add a ">= 0" constraint.
  2. Constrain primarily where it's necessary to maintain referential integrity.

Consider the following diagram. It's a map of the flow of data from your user, which eventually makes its way into the database.

Validation Layers

Since we're getting input from a user, and they're the one that can fix invalid data, we validate data at the top layer. There's usually no getting around this. In fact, for the best user experience on the web, you're going to perform some JavaScript validation. Then you'll probably validate it again on the server, in case they have JavaScript disabled.

At this point, unless there is a bug in your code, you're sure that the data is valid. You may not know if it's referentially valid. Validating the input a third time in the database is probably overkill. It's also a potential performance bottleneck.

Yes, there are many times when this doesn't apply. For example, when multiple systems are interacting with the same database, and one counts on the data in a certain format. The only way to guarantee you get data in a format you expect is to constrain it at the database level.

In general, I avoid strict constraints at the database level. The biggest reason is that it requires your to synchronize all of your validators. They all have to agree on the same set of restrictions, or the code will fail. That goes against the LEAN and Agile philosophies. When I want to allow negative numbers in my integer field, it's much easier to simply change it in my application. This is amplified if you have to talk to a DBA to make changes.

Another reason to avoid constraints is that they can't always understand the data like the application can. For example, should a constraint attempt to ensure that valid email addresses are entered? If you're storing a persons age, do you constrain it so that it can't go above 500? 200? 100?

iStock_000005716223XSmall

Now let's assume that there is a bug in your application code, and you didn't have a trusty constraint to stop it. You now have invalid data in your database. The good news is that you now have the potential to clean it up, or adapt your code to deal with it. The bad news is that if that value is used in a calculation that could have bad side effects, you could have big problems.

As with anything, there is no hard and fast rule for every situation, but we can at least make some general guidelines.

Think LEAN. Anything that doesn't provide value to the customer is waste. Think Agile, requirements change, be adaptable.

Like this post? Please share it!

See a mistake? Edit this post!

Windows 2008 and Visual Studio 2008 Launch Event

I attended the "HEROS happen {here}" event in Milwaukee Wisconsin. This launch event covered Windows Server 2008, Visual Studio 2008, and SQL Server 2008. I'm including my notes here in case they are of use to anyone.

Microsoft always does a good job putting on events, and this was no exception. There were a lot of third party vendors, and there was no lack of freebies from all. Microsoft gave everyone a nice lunchbox with a decent breakfast inside.

Microsoft also gave out copies of Vista Ultimate SP1, Windows Server 2008 Enterprise (including a virtual key), and Visual Studio 2008 Standard. Stay tuned for a post where I'm going to have details about giving away some of this software to you.

500563220_a2b1f718f9

Windows 2008 Server

  • Powershell can easily manage remote machines, including executing a command line remotely.
  • Group policy is now a serviceGroup policy now has extensive logging
  • Custom group policy (preferences) allows you to arbitrarily add registry settings or files.
  • RSAT - Allows you to manage your 2008 policy settings with Vista.
  • WinRS is basically an SSH alternative for Windows.
  • You can set up a custom view to filter (search) for event log events for all computers in your network.
  • The old 64KB receive Window limitation has been replaced with a receive window system that auto-tunes. This provides a significant increase in network speed. The benefit increases as the latency increases.

Virtualization & Hyper-V

  • The feature set appears to be one step ahead of VMware Server in many ways. For example, you can do CPU throttling and multiple snapshots.
  • There is a version that will give you a bare bones version that is ONLY Hyper-V, and it's only 40,000 lines of code.
  • Application Virtualization (SoftGrid)
  • Requires VT extensions
  • Can be scripted using Powershell
  • Based on the OS you buy, you can get free Windows licenses for the virtual machines. For example, if you buy Datacenter Edition, you can run unlimited Windows 2008 virtual machines on it without buying multiple licenses. Enterprise edition covers 4 virtual machines. Standard covers 0. You actually get virtual keys for the virtual machines.
  • Licensing is per socket, not per core (which is the same as VMware licensing)
  • Terminal services features EasyPrint so that virtual printers don't have to be installed. It uses XPS (think MS PDF) as a common communication format.
  • You can set up a terminal services gateway to provide access to any terminal server behind your firewall.

image

Visual Studio 2008

  • MOSS is very popular right now (really?).
  • Expression Web just released version 2.0.
  • HTML 5 is about to be finalized.
  • CSS 2.1 is about to be finalized.
  • Visual Studio allegedly writes decent cross-browser XHTML code.
  • ASP.NET starter kits are available at www.ASP.net, and look pretty interesting.
  • The AJAX control toolkit has some very impressive controls that let you easily add functionality to your pages. For example, there is one that adds a watermark to a TextBox. Another confirms button actions (delete for example).
  • Test inheritance
  • The ScriptManager can localize formats
  • Easily call server methods with JavaScript
  • New profiling tools
  • More tools are available in Professional that used to be only available in higher editions.

Like this post? Please share it!

See a mistake? Edit this post!

Set up a Windows SSH tunnel in 10 minutes or less

Here are step-by-step instructions for setting up a quick and dirty SSH tunnel to another network. This gives you the ability to connect to any machine and port that the remote machine has access to. The process is so simple, it should take you less than 10 minutes.

Why would you want to do this? Well, it's a pretty slick way to connect to a remote machine on a single port and have access to all of the network resources of that machine. Your connection acts like it's originating from the server. As a test, I set it up on my development computer running Visual Studio. Visual Studio runs web projects in a special web server that is only accessible from that machine. Using SSH, I was able to make it accessible to anyone on my network using my computer as a gateway.

SSH Diagram

In this example, we're going to be using two free pieces of software. On the server, we're going to use a version of OpenSSH that uses a stripped down version of Cygwin to run on Windows. On the client, we're going to use Putty.

The first step is to download and install both programs. Here are the links to the download pages for each product:

On your the server, where OpenSSH was installed, we need to run a couple of quick commands at the command line (go to start->run and type "cmd"):

  • mkgroup -l << ..etcgroup
  • mkpasswd -l << ..etcpasswd
  • net start opensshd

You're now running an SSH server! Now we can configure the client (Putty).

Open up Putty. It doesn't require installation, so you can simply double-click on the executable. In the tree on the left, navigate to Connection->SSH->Tunnels. We're going to add a list of local ports that will be routed to machines that the server has access to. For example, if we want to access remote desktop on the remote machine, we can put a destination of 127.0.0.1, and a port of 3386. The source port is the port that we'll connect to on our machine. Remember to click "Add" when adding a forwarded port.

SSH Tunnels in Putty

You should end up with something like this. Now, go to the "Session" section in putty. Enter the host name of the server you want to SSH into. Then, select "SSH" as the connection type. Then press "Open". You'll be prompted for a username and password, which should be an account on the server you're connecting to.

Putty Host Name Screen

The beauty of this is that you don't have to set up the port list on the server, it's all configured on the client. You can even let computers connect to your computer and have them be routed to the remote machine.

Hopefully that is enough information to get you started. I'm no SSH expert, but it was pretty easy to get set up when doing it this way.

Like this post? Please share it!

See a mistake? Edit this post!

Jason Young I'm Jason Young, software engineer. This blog contains my opinions, of which my employer - Microsoft - may not share.

@ytechieGitHubLinkedInStack OverflowPersonal VLOG