Naming of Foreign Keys, Indirect Keys, External Keys

sqlCVS needs to be able to look at a field and know what type of field it is--a primary key, foreign key, external key, indirect key, or some data field. Therefore the fields must conform to a naming convention.

Foreign Keys

They must be in the form FK_tablename[_optional data], where tablename is the exact name of the table they refer to. You can add an underscore followed by some optional tag. For example, if you have a table Customer and a table Address, and Customer has a foreign key to address, the name of the field would be FK_Address. If you wanted to have 2 foreign keys to Address--maybe shipping address and billing address--you could have FK_Address_Shipping and FK_Address_Billing. When sqlCVS looks at a field that starts with FK_ it tries to find a table with that name and works backword with each underscore. So when it sees FK_Address_Shipping, it would look for a table "Address_Shipping". Since it doesn't exist, it drops off the last _ and looks for a table named "Address". That does exist, so "FK_Address_Shipping" is considered a foreign key to the table "Address". If sqlCVS cannot find the table referred to by a field that starts with FK_, it will abort and tell you this.

Primary Keys

They must be in the form PK_tablename, where tablename is the name of the table for which it is a primary key. The primary key is normally an auto-increment field, although it doesn't have to be. sqlCVS's definition of "primary key" is different from SQL's, since a MySql primary key can really be one or more foreign keys. So, if you have a table Customer and the primary key is the customer's id, the primary key would be "PK_Customer". Then you have a table Language to hold all languages, with a primary key "PK_Language". Next you create a table Customer_Language to give you a many-to-many relationsip that shows all the languages the customer speaks. Customer_Language would have 2 foreign keys "FK_Customer" and "FK_Language". Both foreign keys together would be the primary key for MySQL. As long as you follow the convention sqlCVS will understand this. Maybe you also have a table Customer_Preferred which has some extra data just for Preferred customers, but uses the same primary key as the main Customer table. So the MySQL primary key is really a foreign key, which you would call "FK_Customer".

Indirect Keys

This is a concept that does not appear to be a database standard where you use an intermediary table to indirectly store properties or foreign keys. In LinuxMCE's database, for example, we have a table "Device" which has all the devices in the home. Every device requires a variable number of different parameters. For example, our media directors take a parameter "PK_Language", which is actually a foreign key to the Language table indicating what languages you want your on-screen menu's in. Cameras have a "sensitivity" parameter indicating the sensitivity of the motion detection algorithm. You can't practically add an unlimited number of fields to the Device table for every type of parameter for every type of device. So we have a DeviceData table which has a fields "PK_DeviceData" and "Description". The rows may be 1-"PK_Language" and 2-"Sensitivity". Then there is a table "Device_DeviceData", which has