With SPB16.2 release, Capture-CIS allows you to create and use relational tables in the parts database. These tables have a one-to-many relationship with part information (primary) tables. For example, the database may contain a Vendor table with multiple vendor / manufacture part numbers for one company part number in your Resistor table. This structure allows you to query for data across the primary and relational tables.
What is Relational table support and how it works
Short for relational database management system, RDBMS and pronounced as separate letters, is a type of database management system (DBMS) that stores data in the form of related tables. Relational tables are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several tables.
How it works in Capture-CIS
Capture-CIS allows you to create and use relational tables. You can define the primary - foreign key relationship between the parts (primary) tables and related tables in the database. In the Relational Database tab, you define the relationship between the part (primary) and relational tables in the database.
Relationship through Part Number between Capacitor table and Vendor table
Note: Capture CIS supports a one-to-many database relationship between the part information tables and related tables.
The Set Relational Data grid contains the following fields:
Primary Table Name: Use this list to define the part (primary) tables in your relational database. This is the only read-only field in the grid.
Primary Key: Use this list to define the primary key that you want to use to form the relationship with the relational table.
Relational Table: Use this list to specify a relational table that has a primary - foreign key relationship with the selected primary table.
View Name: Use this text field to define a friendly name for the view that will display when a designer selects the primary table to create a relational query. In this case I have defined it as CapVen.
To define the relational data:
First, ensure you are in the Relational Database tab of the Configure Database dialog. Go to a row that contains a primary table.
Select the Primary Key field drop down list. This list displays all the fields in the primary key table. Note: If the specified ODBC driver provides support for retrieving the primary key, this will be displayed by default in this field for the corresponding primary table.
Select the field to use in your relation.
Select the Relational Table field drop-down list. This displays the list of tables in the database that have a primary - foreign key relationship with the corresponding primary table.
Select the table to form the relationship with the primary table.
In the View Name field, define a friendly name for the view that will display when a user selects the primary table to create a relational query. Repeat above steps for every table in Primary table list that is to be defined as a primary table in CIS and you want to create a relationship with a relational table.
How will the you see the relationship in CIS?
In Capture-CIS the relation table can be viewed using the View Name, for example : CapVen
The Capacitor table and Vendor table can be viewed using Part Number.
As always, I look forward to your feedback on how you employ this new capability in Capture-CIS.
Jerry "GenPart" Grzenia