Database column naming convention

I think this might come off as more of a gripe than a feature request, but it’s a request none the less.

Can you conform to some naming convention/standard.

To name a few issues I have -

Some column names use an underscore as a delimiter, others do not.
ID columns are sometimes called id, other times called tablename_id.
Tag columns are sometimes called tagpath, other times called path.
String value columns are called stringvalue or strvalue.
The alarm_event_data table’s column ‘id’ isn’t a primary key, it’s a foreign key for alarm_events, alarm_events_id might have been a better name for this column?
The alert_log and audit_events columns are shouting at me :wink:


Man, I am totally with you on this one. After doing lots of database work in the past, I’ve found that standardizing is a big help. Here are a few things I do that help me in this respect.

  1. I always make the first field in a table an ID field that is auto-incrementing and is the Primary Key, whether it is needed or not. I always name it the table name plus ID. No underscores for me. A capital for the first letter of the table name, and ID is always capitalized. Example: table name = Devices, ID field = DevicesID. I never have to wonder what the ID field is in a table. It is always the table name plus ID.

  2. Anytime I join this table with another table, I use this ID field for the join. It is unique and will always work, no matter what you do with the other fields. I found out years ago that joining on a text field is not good. We had a table called Meters, and we were using an alphanumeric field called MeterNumber that was a foreign key in another table. Well, the company decided they wanted to change the meter numbers to a new convention. Guess what we had to do? The table now has an ID field called MeterID that is the new foreign key for other tables and we can change meter numbers anytime we want, with no repercussion.

  3. Foreign key field names in tables are always the same name as the ID field they are referencing. So, taking the example above, if I reference the MeterID field of the Meters table in a table called Area, the foreign key field in the Area table will be called MeterID. There is no doubt what table that foreign key is referencing, and what field in that table it is referencing.

  4. I never use underscores. This is just a personal preference. I merge words together using a capital letter at the beginning of each word to distinguish them. Example device name = DeviceName. It just looks cleaner to me. Your mileage may vary.

  5. I never use reserved words for field names. For instance, if I have a date field, I can name it Date in MSSQL by putting it inside braces such as [Date]. Instead, I call it something like TheDate. If I don’t, sure enough, down the road I am going to forget the braces and something is going to fail because of it.

No, I’m not totally OCD. Damn near it though. :laughing: And I’m not suggesting you do as I do. I’m just stating what I do to standardize all my stuff and make it easy on myself. There is no doubt in my mind that it helps.

Another thing that kind of goes with 4 and 5. I never put spaces in field names. That requires you to use braces around the field name so SQL doesn’t get confused. If the field name needs to be two words, such as last name, I use the caps on the first letter method to merge them together, as in LastName. Again, just my preference.