Before you can store information using a computer, you need to think about exactly which pieces of information you are going store, and what type of information they are.
Fields, Records and Tables
The first thing you need to think about are the fields you are going to use. Fields are the categories of information that your database is going to store. For example, most schools give out merits to students who have worked well. To produce a merits database, you would include fields such as Student Name, Tutor Group, Subject, Date, Reason for Merit, Teacher, etc.
Once you've decided on the fields you're going to use, you can start entering the data. All the information for one person or thing, i.e. the information for all fields gathered together, is called a record. In the merits database example, therefore, each student who had a merit would have a record in the database.
All the records are then gathered together in a table (or the file, if you're using a flat-file database).
Field Types
Most database programs allow, or rather require, that you give each field in your database a type. The field type indicates what type of information is going to be stored in that field. Common field types are:
- whole numbers
- decimal numbers
- text
- dates
- Boolean (or yes/no)
Some more advanced databases also let you store multimedia objects, such as pictures or sounds.
Field types form a basic type of validation in that the database won't allow you to enter, say, text in a date field. They also facilitate sorting, as the database "knows" more about the contents of the field. If you were to store numbers in a text field, for example, and then sort them, 11 would come before 2 because the character 1 comes before 2, and the numbers would be sorted in alphabetical order.
For our merits database, then, we would use text for the name, tutor group, reason, teacher and subject, and a date field for the date of the merit.
Key Field/Primary Key
In order to produce a logical structure for your database, there should be something unique about each record in your database. This will normally be the contents of one particular field, called the key field. For example, if you had a car database, then you could use the registration number as the unique key, as no two cars should have the same one.
Sometimes you can't identify a single key field in your database table. In the merits database, for example, there would be no one thing about a merit that would be unique. It couldn't be the date, because there could be more than one merit given per day. It couldn't be the teacher or the student, because that would mean they could only give or receive one merit ever. It's even unlikely to be the reason, as some teachers are just going to enter things like "Good work!".
In cases such as this, you can create what is known as a compound key - a combination of fields that is unique. This can be any number of field, but should be the minimum number required to produced a unique description of the record. In the merits database, for example, you could go for a combination of student, date and subject. A student might get more than one merit, ever. He/she might get more than one for a particular subject, and possibly ever two on the same day, but it's probably unlikely that he or she will get two merits on the same day for the same subject.
Indexes
The final thing you can create for your fields is an index. Once you've started using your database, the data can be stored in the table in any order, probably the order in which you entered. If you've got a lot of records in your database, then sorting and searching could take a long time. An index is just like the index in a book - it is an extra bit added on to the database to help the database program find records quickly. You are discouraged from indexing every field in your database as there is an overhead involved when creating the records, but you should index all key fields and any fields that you regularly use to search or sort.