Overview

Databases are formed, primarily, from tables with each table containing columns and rows similar in appearance to that of a spreadsheet. This section will detail some of the things that you need to keep in mind when creating databases and the structure contained within them.

Creating the Database

The very first thing is to create your database.  This is probably the easiest part but here is a list of things that you should keep in mind.

How much data will be stored in the database?

– Database files can become pretty big depending on the amount and type of data it will be storing. If you are storing file data within the database then you will need a lot more space for the database file to grow. Ensure that the locations of the database files are in a place where they will have enough room to grow.

Transaction Logs

– By default transaction logging is set to Full when you first create a database. If you do not need transaction logging then you should consider switching this to Simple sooner rather than later. Remember: transaction logs can get very big, very fast and can often be many times the size of the actual database.

File Locations

– If the database is going to have a lot of systems connecting to it and you have transaction logs enabled, it could be worthwhile storing the database file and transaction log file on separate physical disks.  This will help with performance when it comes to I/O hits to the disks themselves. Remember, disks buffer their requests the same as most other things and can only perform one I/O task at a time. Having the files on separate disks will allow the log file to be updated at (almost) the same time as the database file.

Collation

– Collation tells the database how to store the data depending on the character sets being used.  Most of the time you will have no need to touch this but it is useful to know a few key facts:

• Once the collation is set for a database it can be tricky to change (especially when the database is already populated)
• When you are joining data from one database with another, if the collations are different, you will need to specify collation conversion when making the join and possibly when you interact with the data
• The most common collation is Latin 1 General (Case Insensitive, Accent Sensitive).

Summary

That’s it for this post. It is only a small taster of some of the key things you should consider when creating your database. I hope this post is helpful and as usual, constructive criticism and suggestions are welcome.