Database Relations
Relations are links between tables that contain related data. We talk about "many-to-one" relations and this means that many records from one table can be related to one record in another table. Think of this as a mother-child relationship: each mother can have many children, but each child can have only one (birth) mother. Examples of relations include:
- Companies and Products (one company makes many products)
- Parents and Children (one mother or father has one or more children)
- Authors and Books (one author has written many books)
It is also possible to have a many-to-many relationship. For example, many products might be made up of many parts – one part might be used in a number of different products, and one product might use a number of different parts.
In CatBase, both types of relation are possible.
How Relations are created in CatBase
The relationships are already established – you just need to know how to use them! To illustrate, we will look at our Demo Database, which contains data on Photographers and Pictures.
If you open any Photographer record and go to the Pictures tab you will see an included list:
This is a list of records from the Pictures table that are related to this Photographer's record. You can double-click on one of the picture names to open up that record.
To add a new Picture record for this Photographer, click on the Add button. A pop-up menu appears, with two options:
Choose Create a new Pictures Record if the record isn't already in the database, or Add an existing Pictures record if you want to add a Pictures record that already exists in the database). (Of course, your database may have different names for these tables).
If you chose to create a new record, you'll then see the data entry window for that table. Enter the details and save the record, and it will be added to the included list.
If you chose to add an existing record, CatBase will then ask you which record you want to add; enter the record name (the Picture name, in this case) and click OK. The program will look for records that match what you entered and if it finds one, will add it to the included list. If it finds more than one, it will show you a list and ask you to select the appropriate record. Note that you can enter a partial record name and CatBase will find any close matches.
You can also view a record's related data by clicking on the magnifying glass icon in the button box at the bottom of the screen:
Click on the magnifying glass, choose the table that you want to see relate records for, and CatBase will open a new window listing them.
Another way to view related records is from the list view. This option can be handy if you want to see all the related records belonging to a group of "parent" records. Suppose, for example, you want to see all Pictures belonging to all Photographers based in the UK. You would:
- Make a list of all Photographers
- Select the ones in the UK
- Click on the button, at the bottom of the list, labeled Show Pictures
CatBase will find all the Pictures related to all the selected Photographers and display them in a new window.
(The table names in your own database will probably be different).
How to Publish Related Data
Example One: Putting the Related Data into a table
When you are ready to publish your data, you can include related records by using the Relation Element type in a Publishing Style Sheet.
As an example we will look at the Photographers and Pictures - 2 Publishing Style Sheet in the Demo database. This style sheet creates a catalogue listing Photographers, with a list of each photographer's Pictures.
- On the main CatBase window, click on the Style Sheets button and choose Publishing Style Sheets from the pop-up menu.
- In the Search Dialogue window, click on View All Records
- Double-click on Photographers and Pictures - 2
- The Photographers and Pictures - 2 Publishing Style Sheet opens. It comprises 6 Paragraphs:
- Double-click on Paragraph 6, "pictures"
- This Paragraph consists of one Element. Double-click on it and you'll see that the selected Element Type is Relation:
- If you click on the Relations button you'll see the available Relations for the table on which this Publishing Style Sheet is based (Photographers).
- The Relation area shows a list of Paragraphs for the related table; in this example, there is just one Paragraph for the Pictures table.
- Double-click on the "picture details" paragraph inside the Relation area.
- The Paragraph opens up. It comprises four Elements:
- If you click on the Preferences tab you'll see that this Paragraph is going to be put into a table.
So, when CatBase publishes the data using this Publishing Style Sheet, it will:
- Publish the required details for each Photographer (Name, Country, Specialty, Telephone, Email)
- Find all the related Pictures records for each Photographer
- Sort those Pictures records by name
- Create a table containing the required details for each Picture (Name, the framed price, unframed price, and a special comment if there's a Special Promotion on for a particular picture.)
Here's an example of how the details for one photographer will look when placed in an InDesign document:
 |
The Photographer John Bird has six Pictures: i.e. there are six Pictures records related to one Photographer record.
In this example, we've put all the related records into a table. |
Example two: Creating Letters containing the related data
In this example, we are going to send letters to our photographers and we want to include some information about each of their Pictures. We're using the Publishing Style Sheet Update Letters in our Demo Database. It looks like this:
- Paragraphs 1 through 4 will publish the photographer's name and address.
- Paragraph 5 inserts the date.
- Paragraph 6 will insert the photographer's first name in a "Dear ..." line.
- Paragraph 7 inserts an introductory text.
- Paragraph 8 is the interesting one: it finds all of the photographers related records (their Pictures), sorts them by name, and publishes their names and prices in a list.
- Paragraph 9 finishes up the letter.
We've selected RTF as the default publishing destination this time, so that the letters can be opened in a word processing document such as Microsoft Word and quickly printed. Here's a sample letter:
These examples show just a couple of ways that data can be published with CatBase. You can publish data to a variety of destinations: QuarkXPress, InDesign, XML, HTML, delimited text files, and (Data Chameleon Edition) other ODBC-compliant databases such as Microsoft Sql Server, MySql, etc.
What to do next
