02 - CIS Part Database

The part database is the entity that contains all the relevant information about the parts you use in your designs. It consists of one or more tables, with each row representing a part and each column representing a part property.

Apart from the tables containing part property data (henceforth referred to as primary tables), your part database may also include tables with relational data. For example, your parts database may contain a vendor table to support handling multiple vendor or manufacturer part numbers per unique company part number.

Capture CIS supports a one-to-many database relationship between the part information tables and related tables.

Before you create your part database, you need to choose whether to use a database or spreadsheet application. You can create a database from an existing Capture design.

When you create your part database, you need to carefully set its structure and organization, including:

• Part property assignments. 
• Field formats for properties. 
• Number of tables used in the database implementation.

Using ODBC, CIS interfaces directly with your ODBC-compliant database or spreadsheet. This means you can use your preferred application to create and maintain your part database. You can create a database from an existing Capture design. You can also extract information for your part database from an MRP database.

Database Format

To take full advantage of the speed and power of CIS, you may want to use a database application rather than a spreadsheet application. As a general rule, you should use a database application for databases with more than 1,000 parts. Database applications offer the following advantages over spreadsheets:

• Comprehensive data management features
• Better performance for part searching
• Form-based entry for entering part information
• Safeguard against duplicate part numbers

The limitations of spreadsheets and delimited text files include:

• Lack of structure: During data entry, spreadsheets allow you to configure every cell in a different format. This makes querying fields (columns) very difficult because CIS expects queried fields to be in a consistent format.
• Lack of ANSI SQL compliance: Because databases are ANSI SQL compliant, their drivers do very little work in relaying SQL commands to connected data sources. In contrast, with non-SQL compliant spreadsheets, the ODBC driver is responsible for translating the SQL commands into functions that the spreadsheet can understand. This results in a dramatic increase in the time your queries can be processed. Query time increases are even more dramatic with text files because the driver must search for and compile a matching list entirely on its own. An optimized, SQL-compliant database format is several times faster than a spreadsheet with more than 100 parts or a text file of over 20 parts.
• Lack of replication support: If you have users in more than one location accessing your database, you have either a single, central database or a replicated database. Replication is a method of storing a single database in more than one location. True databases support scheduled synchronization: they maintain data integrity by checking separate database transaction logs and replicating modifications on multiple systems. If you are using a spreadsheet or text program, you must check manually because your format does not support replication.
• Lack of concurrency: If multiple users are to access the database, then you cannot use spreadsheets as your database because concurrency is not supported in a spreadsheet application.

CIS Database Feature Support

CIS supports any underlying database application that you connect via your ODBC connection. However, when creating your parts database, keep in mind the following points when defining the fields and tables in the database.

• Use of SQL in CIS Databases
  CIS uses structured query language (SQL) to query, update, and manage the CIS relational databases.
• Supported Data types for the Part Name field
  • CHAR
  • VARCHAR

  • LONGVARCHAR

    If a CIS table defines a part name field with an unsupported data type, CIS will throw an error during the CIS configuration procedure.

• Supported Data types for fields other than Part Name
  CIS supports the following data types for any field defined in the tables of the parts database:
  • CHAR
  • NUMERIC
  • DECIMAL
  • INTEGER
  • SMALLINT
  • FLOAT
  • REAL
  • DOUBLE
  • DATETIME

  • VARCHAR

    If a CIS table defines any field with an unsupported data type, CIS will throw an error during the CIS configuration procedure.

    If a CIS table defines any field with a DATETIME data type, the CIS will not display any error during the CIS configuration procedure, but will display these fields as empty in the tables of parts database.

• Use of Double-quotes in table and field names
  CIS uses double quotes (") as the default field and table delimiter for database queries. This is the standard query delimiter supported in popular ODBC databases such as Microsoft Access, Oracle and SQL Server.
  If your ODBC database does not support double quotes as the query delimiter, database queries will fail. For such databases, you can specify the default field and table delimiters by adding the following two options under the [Part Management] section in the CAPTURE.INI file:

  [Part Management] 

  Field Qualifier = <field delimiter>

  Table Qualifier = <table delimiter>

• Use of Uppercase function
  CIS uses the database uppercase function to convert string variables in queries to uppercase. If the uppercase function supported in your database is not supported by CIS, database queries may fail. You can specify the corresponding uppercase function supported in such databases, by adding the following option under the [Part Management] section in the Capture.ini file:

     [Part Management] 

     Upper Case Function = <name of function>