All,
I have debated with myself for years over field naming conventions - first in FP/VFP - later in SQL Server. I am getting ready to build several new applications and want to re-examine my standards one final time before proceeding. I and would appreciate any opinions anyone has.
My current method involves designating a 2 or 3 character identifier for each table - for example the 'customer' table might be 'cs' - invoice might be 'iv'. All fields are then named 'cs_something', 'iv_something', etc. Pkeys (always surrogate integer) are named 'cs_id'. Fkeys - by convention, are named like 'iv_csid'.
The advantages/pros to this - which is why I use it - include:
- all fields are instantly identifiable with the table they belong to
- pkeys and fkeys (and child/parent table) are instantly recognizable
- code can "count-on" standardized naming of keys and interpret if a field is a key and what kind if necessary
- no confusion or need to specify table names when doing joins
- little to no chance of using reserved words/commands for field names
The cons - which is why I might be open to change include
- can run out of meaningful 2/3 character identifiers in large databases
- a little more typing when entering field names is required
- and perhaps most importantly - this seems to be contrary to "industry standards" to some degree and is not easily or well supported by most case tools I've seen - especially for auto-migration of keys, etc.
I'll look forward to hearing opinions on this.
Thanks,
Ken B. Matson
GCom2 Solutions