There’s a difference between a database and a database package. Microsoft Access, to take the most common example, gives you a simple, easy-to-use application capable of storing your information, recalling key bits of data whenever you need it, and leading you by the hand through Wizards, tutorials and user-friendly dialog windows as you learn how to do it. MySQL is a rather different proposition. Put simply, it’s just the database. It holds your information ready for other programs to hook into, and build their own individual services over the top.

As a result, getting started with MySQL can be a trying process. You’re expected to know what you’re doing from the off, and there’s little hand-holding if you encounter difficulties. We’re going to start from the very beginning, installing and configuring all the software that you’ll need to manipulate your database, before hooking it up to some basic applications and taking control of its data management abilities.

The OS has it
MySQL is available for both Windows and Linux. We’ll be using the former for this series, although most of the software we’ll be looking at is available on both platforms. You can find the download at www.mysql.com/download. It’s both free and open-source, although commercial versions are available elsewhere on the site.

Download and unzip into a new directory, then run setup. Once this has completed, navigate into the main directory – C:\mysql\bin is the default. You’ll see several programs. Of these, the most important are mysql.exe, which opens up a terminal to interact directly with your database, and winmysqladmin.exe for taking a graphical look at it. By default, MySQL contains two databases. Mysql contains its own settings, such as users, while test is a blank slate that you can experiment with. Assuming that everything was properly installed, you’ll be able to browse through the contents of both.

First edits
However, winmysqladmin suffers from a couple of problems – it’s old, and frankly not very good. Now that you’ve checked everything’s working, return to the homepage and download the Control Center from dev.mysql.com/downloads/mysqlcc.html. This has its own installer, and will automatically hook into your MySQL databases. Unlike Winmysqladmin, Control Center benefi ts from a far greater focus on actually doing things with your database, rather than just looking at it.

Let’s begin with the obvious – adding a table into the Test database. To do this, right-click on it and select ‘Connect’. A tables menu will appear next to it, and you can right-click on this to begin specifying what data we want to hold. Note that we’re not actually putting that data in at the moment, simply specifying the fields that we’ll need to fill in.

Each table needs a unique identifier, known as the ‘Primary Key’. You can have multiple people called Brian, you can say that anyone you like has brown eyes, but you can only have one Entry #1 in the list. This doesn’t have to be a straight number, but that’s the most common way to begin.

Start by selecting the top-most field. Choose the first box and call it ‘id’. On the right-hand side, choose integer as its type. At the bottom of the screen, you’ll see the option to automatically increment this field, ensuring that each record has a unique number to identify it. Right-click on it and select Add Primary Key. The next field is dedicated to a particular piece of data. The default type is varchar – to make things easy – a variable length string. Click on this to see the many other available types, including date, time, binary, doubles, fl oats, decimals, blobs and bits. We’ll call this one ‘name’.

Save the table as USERINFO and return to the main screen. Time to add some data. Double-click on USERINFO to open up the relevant screen, right-click and choose Insert Record. As we instructed, each new record will be added with an auto-incrementing ID, leaving us with just the name to fill in. Add a handful of names to populate the database.

We can easily go back and modify the table, perhaps adding a whole new field, without having to start all over again. Let’s add a space for a telephone extension. While we can use any number to represent this, we can be rather more specific about what we’re asking for. If we know that the extension is going to be four digits long, we can set it to be the maximum length, and thus help reduce the risk of invalid data. Simply enter ‘4’ in the length box, save the changes and return to the data input screen. Give each person a suitable phone number.

Many to one
Individual tables can be useful for holding data, but are only a small part of creating a functional database. However, this information can only really come into its own when multiple tables are connected together into a proper relational database. We’ll be looking at this in more detail next month, but the upshot is that all connections are split into three types – 1:1, 1:n and n:m. In a 1:1 relationship, one single data record connects up to a single second, such as a first name and surname. In practice these are rarely used, because such information could easily go in one table. Their real use comes when the tables need to be split for security reasons – publicly-accessible name and job titles in one table, linking up to wages, NI numbers and other confidential information.

1:n – one to many – is far more common in day-to-day use. Keeping with our example, we could have a separate table holding departments in a company. Any or all of them can be in Accounting, Computer Services or the Typing Pool, but we can’t split Accounting into the same number of pieces. The final kind is n:m – many-to-many – such as assigning single or multiple authors to books. In each case, the tables are connected up using two keys – the Primary we created earlier, and a Foreign key that links them together.

When developing a database, it’s important to make use of the three normal forms. These are guidelines that you follow to create an efficient, functional database without any added cruft. The first normal form starts with our Primary Key - each data record must be identifiable. Next, a table has to be created for each group of data, and columns containing similar content have to be eliminated.

Second normal form is to split any repeated columns into separate sub tables and link them up via foreign keys. Finally, third normal form is nice and simple - anything not directly related to the primary key has to be peeled off into its own table. While following these rules isn’t essential to create a basic database, they are worth keeping in mind as their complexity inevitably increases. We’ll be looking at a practical example next month, along with the other crucial aspect of database management - how to go about searching and retrieving the information that we’ve stored in there.