Column Names

Column names are used for list and row datatype variables. In this document if list is specified you can usually assume we are talking about lists or rows.

Lists can have up to 400 columns. Column names are case-sensitive (at least you should assume they are).

Lists can be defined two different ways:

• From a schema class:
  
  Do ListVar.$definefromsqlclass('sSQLClassName')
  
  The SQLClassName could be a table class, schema class, or query class. The SQLClassName can substituted with an item reference to the SQL class. The SQLClassName needs to include the library name prefix if the SQL class is in a different library.
• Manually by adding columns to the list variable.
  
  Do ListVar.$cols.$add('ColName',kCharacter,kSimplechar,100)

List Column Names

Lists which are defined from schema classes that are mapped to database tables will use the database column names.

Schema classes which are not mapped to the database (_listdef suffix) use lowercase column names. This is done to make it obvious to developers it is an internal list. Using lowercase column names also prevents code errors caused by case sensitive typos. (e.g. ListVar.SqlClassName vs. ListVar.SQLClassName can cause an error.)

Lists which contain data from Omnis class or object properties use the property name prefixed with the appropriate type.

For example of a list of methods would be defined as follows:

; Define the list
Do MethodsList.$cols.$add('classname',kCharacter,kSimplechar,100)
Do MethodsList.$cols.$add('methodname',kCharacter,kSimplechar,100)
Do MethodsList.$cols.$add('methodref',kItemref)

; Build the list of methods
Do rClass.$appendlist(MethodsList,rClass().$name,rMethod().$name,rMethod.$ref)

SQL Column Names

The range and variety of naming conventions used for database table and column names is huge. Changing existing database tables and columns is often impossible or not worth the hassle.

This section is provided purely for your information. I have been all over the map on database table and column names over the past years.

The database table naming conventions I am currently using are as follows:

• Table names are singular form words. The first letter is capitalized, the rest are lower case. Numeric characters are avoided. If the word or combination of words is getting up to 8+ characters I start dropping vowels. If the word is a SQL reserved word or common word I will drop vowels. (e.g. Usr vs. User, Grp vs. Group)
• A linking table name concatenates the linked table names in the one to many relationship order. (e.g. A book record could have multiple authors, so the author records are linked to a single book record with the bookauthor linking table.

The primary key and foreign key column naming conventions I am currently using are as follows:

• Primary key column names use the table name with the suffix _pkey. (e.g. book_pkey)
• Foreign key column names use the reference table name with the suffix _fkey. (e.g. author_fkey)
• If there is more than one foreign key column in a table referencing the same table I add a meaningful prefix to the foreign key column name (e.g. siding_subtrade_fkey)

Using the _fkey and _pkey suffix makes typing the query join text simple.

WHERE Book_fkey = Book_pkey AND Author_fkey = Author_pkey)

There are several administrative columns which I tend to include in many database tables:

• Active = Active. An integer datatype column which defaults to 1. If a record is not active it is set to zero.
• EditNum = Edit Number. An integer datatype counter which is incremented each time the record is saved to the database.
• InsBy = Inserted By. A character datatype which is set to the UserKey (or UserID) of the user who created the record.
• InsDateTime = Inserted Date and Time. A timestamp datatype set to the date and time the record was created.
• ModBy = Modifed By. A character datatype which is set to the UserKey (or UserID) of the user who last update the record.
• ModDateTime = Modified Date and Time. A timestamp datatype set to the date and time the record was last updated.

There are several common column syntaxes which I tend to use where applicable.

• PersonFormalName = The formal name of a person in the format you would print it on a letter or address label. 'Person' would be replaced with the appropriate subject. (e.g. AuthorFormalName, ContactFormalName, OrgFormalName)
  
• PersonSortName = The name of the person as it might be listed in a telephone book. 'Person' would be replaced with the appropriate subject. (e.g. AuthorSortName, ContactSortName, OrgSortName)
• PersonID = A unique identifier for the person. 'Person' would be replaced with the appropriate subject. (e.g. AuthorID, ContactID, OrgID)

StudioTips Documentation - Copyright 2006 Vencor Software