Ordering Compound Keys, Indexes, and Access Paths
System Architect enables you to specify the order of the columns of a table's compound key or alternate indexes, through the table's Index editor. Similarly, in a logical data model, you may specify the order of the attributes of an entity's compound key or alternate access paths through the Access Path editor.
What Is a Compound Key?
A compound key, often also called a composite or concatenated key, is a key made up of a group of columns that act together to form a unique identifier for a table.
For example, an opera in a cd collection may have a compound key that is composed of four columns -- the Title of the Opera, its Composer, its Performance Date, and its Conductor. These four columns combine together to make up a unique identifier for an instance of a recording.
Ordering the Elements of a Compound Key
To achieve fast access to the data, you may want to order the columns of the compound key – for example, you might want your system to look up a recording based on its Title, then Date, then Conductor, etc.
Indexes are a separate storage structure used to enforce uniqueness of data and provide a faster access path to the data -- something like a card catalog in a library. You create an index for a table by specifying the columns that can be used to locate an individual record that is represented by the table. A primary key is represented by a Primary Key Index.
You may also want to create alternate Indexes , for example, create an index to look up a recording by Tenor, then Title, then Date, or create another index to look up a recording by Soprano, then Title, then Date, and even create indexes with the same columns, but differing orders, for example, Title, then Tenor, then Date.
The equivalent concept to an index in the logical Entity Relation diagram is the Access Path. System Architect provides identical functionality concerning ordering of the attributes of an Access Path.
Suppose for example we have a table called Opera_Recording, with a compound primary key that consists of columns Title, Conductor, Performance_Date, and Conductor, and contains other columns Tenor, and Soprano. We may specify this information in System Architect's column grid for a physical table.
System Architect automatically generates a Primary Key Index based on the columns specified as primary key components. You may view, and alter, the Primary Key Index in the Indexes tab of the table definition. For a compound primary key, System Architect uses the ordering of the columns in the Column grid of a table as the default ordering. You may change this ordering in the Column grid, or in the Index editor itself.
You may create a secondary index for this table by doing the following:
1. In the Indexes tab of the table definition, push the New button, and name the new index -- for this example we choose the name Sort_by_Tenor.
2. From the Column(s) in Table list, choose the columns that you want to be a part of your index, and push the arrow-pointing-right button to add them to the Column(s) in Index list.
3. You may order the columns by selecting one and moving it up or down within the Column(s) in Index list.
4. You may also choose to sort the column in ascending ( ASC ) or descending ( DESC ) order. In this example, we choose to sort Performance_Date in descending order.
In this example, we create a third index, Sort_by_Title :
1. In the Indexes tab of the table definition, push the New button, and name the new index -- for this example we choose the name Sort_by_Title.
2. From the Column(s) in Table list, add the same columns as before to the Column(s) in Index list. But this time sort them so that Title is first.
When you generate schema for your DBMS from this physical diagram, you will generate these three indexes.
That's this week's tip. For more information on keys, indexes, access paths, and relational data modeling in general, please see the on-line help.