- Primary keys are either "natural" or "surrogate" -- you should know the difference, and when to use either.
Notes: This is an important area and additional reading is required if you are not sure. Briefly, an example natural key is Employee_No where the value is assigned by your HR Department. One example of a surrogate key is the GUID, a long string of characters guaranteed to be unique. Another more common surrogate key is an auto-increment integer value (1000, 1001, 1002, etc.)
- Primary keys should include the table name followed by "_id" (or "_no", "_code", etc.) e.g., "product_id". In most cases, all other fields in the table will exclude the table name.
Reason: Including the table name in the Primary Key will allow Alpha to automatically suggest matching key values when adding Linked Grids or SQL Joins.
- Use the field name suffix "_id" for surrogate fields, and "_no" or "_code (etc.) for natural keys. For instance, "item_id" if the primary key is an auto-increment (surrogate), but "item_no" if that Item Number is a natural key, a value provided perhaps by the Manufacturing Department.
- Foreign Keys should be named the same as the Primary Key in the parent table. For instance, in a table named product_detail, name the foreign key to the product table would be "product_id".
- DBF-only: It is common for DBF tables to have been assigned alphanumeric auto-increment values (AA100, etc.) You are encouraged to use numeric field type with auto-increment if you plan ever to convert your tables to SQL.
- If users need to see the primary key (e.g., "Your order number is 100023") it is good practice to start your integer auto-increment value with an "attractive" number like 10000 rather than 1. But technically this is not required.
