The simple answer to this question is:
"A database is an organised collection of information".
You use databases all the time, although you might not realise it! For example:
- Telephone directories
- The list of contacts on your phone
- Online product catalogues and shops
The important thing about databases is the way that the information is organised. Let's look at a simple telephone directory as an example. It has entries like this:
Bloggs, Joe 1234 Main St. ........... (123) 123456
You could simply enter all the information as shown here and then print your directory. The problem is that if you wanted to do something else with the data - for example, organise it in a different way; find all the people in the 123 area code, or publish a different version of the directory, you would have a lot of work to do. But if the information was orgranised in a database, those tasks would be very easy.
Our example listing consists of five different pieces of information:
- Last name
- First name
- Address line
- Area code
- Phone number
- We refer to each of these bits of data as a field.
- Each group of fields belonging to the same item (product, person, company, etc.) is called a record.
- And each collection of records of the same type of data is called a table.
A good analogy is a card index file, such as a Rolodex:
- The whole Rolodex is equivalent to a table
- Each card is equivalent to one record
- And each individual piece of information on each card is a field
Another (more modern!) analogy is a spreadsheet:
- One worksheet is a table
- Each row in the worksheet is a record
- Each column is a field
The structure described above is what we refer to as a "flat-file" database: it's a one-dimensional collection of data about one topic. This is fine for many purposes, but frequently we need to maintain more complex structures in which data from two or more tables is linked together. Typical examples are:
- Families (parents and children)
- Products and parts
- Manufacturers and Products
- Classes and Students
- Customers and Invoices
You might be tempted to think "Why can't I just add more fields if I need more information?" For example, if your database contains information about products and you want to add a list of the components that make up each product, why not just add fields for "component 1", "component 2" and so on? Well, yes, you can, but you'll run into limitations such as:
- Some products might have only one component; another might have 20. How do you know how many fields to add?
- What if the same component is used in a number of different products?
- What if you wanted to find a particular component? You would have to search all those additional component fields, because you wouldn't know which one it might be in!
- What if you wanted to produce a report listing all your components and their prices? That would be extremely difficult.
That's why we have relational databases. In our parts and components example, we would have two tables: Parts and Components. The basic product details would go into fields in the Products table, and all the details about each component would go into a record in the Components table. One more important thing is needed: something to tell the database how the records are linked together - in other words, which components belong to which products. For this purpose, each record must have a unique identifier of some sort. It can be simply a sequential number, or an alphanumeric code of some sort that you devise. The import things are that it must be UNIQUE for each record and it must never change.
The following diagram shows a setup for a simple relationship between our products and components tables:
This illustrates a "many-to-one" relationship: many components can be related to one product via the product id. So, to find all the components that comprise the product whose ID number is 1234, you would search the components table for all records whose product_id is 1234. There might be just one, or there might be dozens.
But what if the same component can be used in a number of different products? That's what we call a many-to-many relationship: many components can be related to many products. To facilitate this, we need to introduce an additional table just to keep track of those relationships. Consider this illustration:
The relations table would contain one record for each link between a product and a component. With this sort of structure, you can easily produce reports showing all the components belonging to a product, or all the products in which a particular component is used.
CatBase handles all these relationships automatically for you: you don't normally need to be concerned with the inner workings of the relationships! You set up your tables and fields in the Table and Field Setup window (click on the Setup button in the main window and choose Table and Field Setup). For example:
Each row in the table represents one of the fields in the Products table; you can add new ones or delete them by clicking on one of the buttons to the tight of the window. In CatBase, the unique reference field for each table is called RecordNumber.
When you are looking at a Product record in this example database, you can see a list of each product's related Components:
Here you can add new components (either create brand new component records or add a link to an existing one), remove a component (either delete it completely or just un-link it from this product), and re-order them.
You can see a more detailed explanation of working with relations in CatBase in the Relations Tutorial.