Overview

A database is made up primarily of tables, a table is made up of columns and rows and each row contains data for each column. When creating or modifying your database structure there are a few things that may save you time.
Tables

Here are some tips for when you are creating or modifying tables:

• Table Naming Convention – It is important to abide by a standard naming convention. Remember that when a developer or administrator is looking for a specific collection of data, the only think they can see is a list of tables
• Schemas – If your database is using schemas then you will need to set the schema when you create the table. You are not going to be happy with yourself if you have written a load of stored procedures and then realise that the schema wasn't set
• Saving – When saving changes to a table try and get into the habit of hitting the save button or using the shortcut key (CTRL + S) rather than hitting the close tab button and then clicking the Save button on the dialog. If the save fails, the tab will still close!
• Auto Generate Change Scripts – Within the management studio there is an option to have it create a script every time you save a table modification. It’s could be worth while enabling this so that when you are performing updates for a database that is already in production, you will be able to save the scripts as you go along
• Prevent Saving Changes that Require Table Re-Creation – When making certain changes, the management studio may tell you that all the data in your table will need to be deleted because of the type of changes that you are making. There is an option (use with care) that stops this from happening and will allow you to just go ahead and make the modifications
• Adding a Non-Nullable Field to an Existing Table – When you try to add a field to a table that does not allow null values and there is data already in the table, it will not allow you to save your changes.  There are two solutions to this depending on your data type:
  • The first option is to also set a default value.  This will go through and set all the existing rows to this value when saving your changes.  You can then remove the default value and save again
  • The second option is to save the field allowing nulls, update the field to a value using an updated script that updates all rows and then switch the field back to not allowing nulls
• Identity Fields – Don’t forget to set the identity setting to true and set the seed and increment values if required

Fields

When adding fields to a table the following may be of help:

• Field Names – As with table naming, always use a standard convention for naming your fields.  It’s a pain for someone who is accessing the data to have to keep looking at the table definition to find out what fields store what
• Data Types – It is very important that you choose the right data type for the job.  There are many to choose from and some of them also have sub-definitions of their own (e.g. VARCHAR has a length definition). Each data type uses a certain amount of space in a table row. Take time to learn what types use what space. If you are storing an enumeration value from an application in a field, the chances are that it is only every going to be a low number so why use an INTEGER that uses 4 bytes when you can use a TINYINT that uses 1
• Data Type Lengths – Remember that when you use a data type that allows you to specify a length or precision, you need to ensure that you solution also has the same constrictions. If you try and insert data into a field that is larger than the type allows, SQL will raise a truncation error and your statement will fail
• Multilingual Fields – If your solution is expected to access character sets from multiple languages then you will need to ensure that all fields that store this data allow an extended character set (e.g. NVARCHAR instead of VARCHAR). However, don’t just use these for the sake of it; in versions of SQL Server before 2008 R2, NVARCHAR fields use more space than VARCHAR fields
• Default Values – Each field can have a default value. If a value is not set when inserting a row, the default will be used.  This is very useful if the row is likely to be created with a very small number of fields initially populated.  You could just allow the fields to store nulls but you need to be conscious of this when returning data back to the client and also when performing joins using the field
• Changing a Data Type – If you are changing the data type on a table that already contains data, it may be that you will need to write a script that will create a temporary field so that you can hold the original data and then move it back, casting it at the same time into the new data type, so that you do not lose your data.

Summary

That’s it for this post. Thinking about the table structures also helps you think about how your solution is going to use the data and what requirements it may need. Remember, a good solid database structure will save you loads of time when creating your Data Access Layer and Business Objects within your solution. We hope this post is helpful and as usual, constructive criticism and suggestions are welcome.