Tips on How To (Correctly!) Define Your Tables

Having a good understanding of how to CREATE, INSERT, UPDATE and DELETE from tables (and views have been covered too) is obviously a massive benefit to your learning curve. However, now that the "basics" have been covered, it's time to fine tune our knowledge and learn more about how to make our databases and objects more intuitive.

Naming Conventions

Naming your views, tables, columns, objects etc. with sensible (and practical) naming conventions is vital, otherwise you may end up alienating your users and losing true clarity on what is stored within your view/table/column/object. To avoid this situation, ensure that you choose one sensible naming convention and then stick to it throughout. There are two common practices:

• Upper CamelCaps (or Upper CamelCase): Where the first letter of each word is capitalised. In pure coding cases, you usually only use standard camelCaps where the first letter of each word is capitalised except the first word. 
• Under_scores: Where words are separated using underscores. 

It's also a good idea to use prefixes before your Views, Functions and Stored Procedures as they become easier to find and subsequently use when writing T-SQL.

Examples are:

• fn_ - Function
• sp_ - Stored Procedure
• vw_ - View 

We tend to not use prefixes on table names any more as it can become clunky as the majority of your database objects will be tables. It's purely to aid the developer and helping to distinguish between all database objects in the system.

Choosing Your Data Types 

Now, it's time to tackle data types and more specifically, choosing the right one for the job.

Below are 5 simple tips for choosing the correct data types that I try to follow myself when designing either a new database or new tables within an existing database:

1. Always use a data type that takes less space to store yet still performs the task that you want it to.
2. Make sure you plan ahead! Think of future application needs when designing your database and once you have added a data type to a column, try not to change it to a different type as it can be difficult to re-reference (though there may be some situations where you will have to change the type - again keep this in mind). 
3. In the majority of cases, use variable-length data types as they adapt to fix the size of their contents. A perfect example would be to use nvarchar instead of nchar.
4. Use fixed-length data types if the contents of a column changes regularly as the cost of re-allocating and de-allocating space far outweighs the cost of recommending a larger amount of space to begin with.
5. Consider the practices of the database in pre-existing tables and follow their example. This will make things less confusing. For example, if the database uses GUIDs as unique identifiers instead of auto-incrementing integers, try to stick to using GUIDs. 

Below is a link to Microsoft's site, listing database types that are available in SQL Server. Use this wisely to help you choose the best data type for your situation.

http://msdn.microsoft.com/en-us/library/aa258271(v=sql.80).aspx 

NULL or NOT NULL?

When deciding whether a column should be NULL or NOT NULL, make your decision carefully, especially in the case of allowing null values to begin with and then modifying the column to NOT NULL. If NULLs have been entered into the system and then the field becomes not nullable, it means that those database records would now have data missing and SQL Server will kick out an error message stating that the type cannot be changed. It's better practice to start with using NOT NULL in the beginning and then switching later if you have to. 

What you should also consider is that allowing NULLs will allow gaps in the database and it can make it confusing when trying to query the database. 

Plus, NULL is what it is... null. 0, unknown, N/A, none and -1 are all values and do not mean the same thing as NULL so beware! (particularly when using aggregates as this can mess with your result set!)