"The program allows us to save a great deal of valuable time by automatically importing images, style sheets and formatting. This allows us to be more competitive in the marketplace and therefore more successful in our business "

– Antiques Trade Gazette

Database 101

What exactly is a database and how do databases work? What's the difference between a spreadsheet database and a "real" database? Read on to find out!

What is a database?
Databasics
Relational Databases
How CatBase maintains relations

What is a database?

The simple answer to this question is:

"A database is an organised collection of information".

Databasics

You use databases all the time, although you might not realise it! For example:

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:

  1. We refer to each of these bits of data as a field.
  2. Each group of fields belonging to the same item (product, person, company, etc.) is called a record.
  3. 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:

  1. The whole Rolodex is equivalent to a table
  2. Each card is equivalent to one record
  3. And each individual piece of information on each card is a field

Another (more modern!) analogy is a spreadsheet:

  1. One worksheet is a table
  2. Each row in the worksheet is a record
  3. Each column is a field

Relational Databases

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:

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:

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.

How CatBase maintains Relations

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.

What to do next ...

About Us | Site Map | Privacy Policy | Contact Us | Affiliates | ©2010 CatBase Software Ltd.