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. Think about a teacher-student relationship: Each teacher has many students, and each student has a number of teachers.
In CatBase, both types of relation are possible.
How Relations are created in CatBase
You create relations between tables in the Table and Field Setup section.
To illustrate, we will look at the Sweet Tooth Fairy project in our Demo Database, which contains data on Products (cakes and cookies) and Variants (options available).
If you open, for example, the Chocolate Chip Cookies product record and go to the Variants tab, you'll see that this product has a few options available:
This is a list of records from the Variants table that are related to this Product record. You can double-click on one of the Variants to open up its record.
To add a new Variant record for this Product, right-click or Ctrl-click on the Variant list area (the striped area). A pop-up menu appears, with various options:
-
Choose Add a new Variants Record if the record isn't already in the database, or Add an existing Variants record if you want to add a Variants record that already exists in the database). (Of course, your database will probably 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's key data (eg its name, SKU, Part Number, etc.) and click OK. CatBase 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.
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 Variants belonging to a selection of Products. You would:
-
Go to the Products table.
-
Find and select the Products that you want to use.
-
Right-click (or ctrl-click) on the list and choose Show Related Variants records.
CatBase will find all the Variants related to all the selected Products and display them in a new window. (The table names in your own database will probably be different).
How to Publish Related Data
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 "Catalog for Word" Publishing Style Sheet in the Demo database.
This style sheet creates a catalogue listing Products, with a list of each product's Variants.
-
Select the Publishing tab at the top of the window.
-
Expand the Publishing Stylesheets topic on the left.
-
Select Catalog for Word.
-
The Publishing Style Sheet is displayed. It comprises 5 Paragraphs
-
Expand Paragraph 4, "Variants"
-
This Paragraph consists of one Element. Double-click on it and you'll see that the selected Element Type is Relation:
The Related Table: popup menu contains a list of all possible relations for the Products table.
The Relation area shows a list of Paragraphs for the related table; in this example, there are two Paragraphs..
-
Expand Paragraph 2: variants table.
-
The Paragraph opens up. It comprises five Elements:
So, when CatBase publishes the data using this Publishing Style Sheet, it will:
-
Publish the required details for each Product (Product Name, Description, etc.).
-
Find all the related Variants records for that Product.
-
Sort those Variants as specified in the Sort Order for the relation (by SKU)
-
Create a table containing the required details for each Variant (Variant name, Description, and Price each.)
Here's an example illustrating how the details for the Chocolate Chip Cookies will look when the data is published:
There are 3 Variants available for our Chocolate Chip cookies.
Learn More
Find out more about Publishing Style Sheets
See examples of some actual projects that were published with CatBase
Download a free, fully functional Trial version and try CatBase for yourself
Got a question? Contact us!