When processing a file with controlling/dependent sets, SQL-CREATE-VIEW creates multiple tables for a single D3 file, which are called a nested set of tables.
The first table created, called the parent table of the nested set, encompasses all attributes which are not part of any controlling/dependent set. This table is given the same name as the original D3 file (with any name changes applied according to SQL naming conventions, see Naming convention differences). The primary key is assigned to the first ADI which references the item-ID. If no ADI references the item-ID, the primary key is the D3 file name followed by _id.
After creating the parent table, SQL-CREATE-VIEW builds a nested table for each controlling/dependent set of attributes in the D3 file. These tables are called nested tables because the data for these tables is nested in one D3 file. The term nested is not part of standard SQL. Since relational databases do not contain MultiValues as in the D3 data model, the concept of the nested table applies only to D3 SQL.
The name of the nested tables is the name of the parent table concatenated with an underscore and the name of the controlling ADI in the controlling/dependent set (again, with SQL naming conventions applied). Besides the attributes in the controlling/dependent set, nested tables also have a special nested key which references the primary key of the parent table. This allows an SQL query to tie the parent and nested tables together in a WHERE clause. For users familiar with SQL, the concept of a nested key is identical to that of the SQL standard foreign key except that nested key implies that the storage of the data is within one D3 file.
For example, the company file has the attributes companyid, name, contact and phone, where contact and phone are a MultiValue controlling/dependent set (many contacts per company – each contact has a phone number). SQL-CREATE-VIEW creates two tables in this case, company and company_contact. The company table has two columns, companyid and name. The company_contact table has three columns, companyid, contact and name.
It is a good idea to use the z option to observe the mapping of a file to multiple tables. If SQL-CREATE-VIEW with the basic syntax is not able to automatically determine the MultiValue layout of a D3 file, the mapping can be manually tuned using the advanced syntax described in Mapping files containing MultiValue attributes.