Introduction
Column names play a pivotal role in ensuring clarity, consistency, and security. Reserved column names are set for several reasons, and these can vary based on the specific needs, practices, and conventions of the organization.
Standard naming convention
It is essential for companies to carefully plan and document their reserved column names, ensuring that they align with organizational goals, industry standards, and best practices.
Variable naming
Naming standards are important because they help keep the code consistent and predictable.
Reference: https://en.wikipedia.org/wiki/Naming_convention_(programming)
Purpose
- To reduce the effort needed to read and understand source code.
- To enable code reviews to focus on more important issues than arguing over syntax and naming standards.
- To enable code quality review tools to focus their reporting mainly on significant issues other than syntax and style preferences.
- To make it easier for someone who wasn’t involved in the project to quickly understand the content of tables and columns.
Data structure and formats
- Naming conventions based on programming language used.
- Standard column naming conventions reflect purpose and avoid abbreviations or acronyms.
- Avoidance of reserved keywords based on software standards.
The following columns should only be used if they follow the structure in this guide
ID: (field)Typically used as a unique identifier for each record in a table.
Guidelines:
Use case: “ID” – format numerically for primary keys.
DateTime: (field) Indicates the date and time when the record was created/modified.
Guidelines:
Follow a consistent date-time format across all records.
Use case: “CreatedDate” Update automatically whenever a record is created.
Timestamp: Records the date and time when the record was last updated.
Guidelines:
Automatically updated upon modification.
CreatedBy/ModifiedBy: Represents the user or system responsible for creating/modifying the record.
Guidelines:
Use a standardized user identification format (System “UserID”). Include user or system identification. Denotes the user or system who created or modified the record.
Active(Is): Boolean value indicating whether the record is active or not.
Guidelines:
Use “True” or “False” values consistently.
Deleted(Is): Boolean value indicating whether the record has been marked as deleted.
Guidelines:
Utilize “True” or “False” values consistently.
Status: Represents the current status of the record, such as “Pending,” “Approved,” or “Rejected.”
Guidelines:
Define a list of allowed status values per system.
Comments: Allows users to add additional notes or comments about the record.
Guidelines:
Support text data of variable length defined by each system.
Location and Address: Defining features for GIS and mapping.
Guidelines:
Standardization for datasets to support mapping functions.
ID field
“ID” as a field name is widespread. However, a more readable and descriptive version would be prefacing your classification. This also allows for foreign key relationships.
| Basic format | Extended format |
| ID | EmployeeID |
Date and Time
ISO 8601 uses 24 hour clock system in hh:mm:ss format (do not use AM or PM) e.g. 13:00 is equivalent to 1:00 PM
Extended format with hyphens is the most human-readable.
Calendar date – April 12, 1985
Date
| Basic format | Extended format | Explanation | Example |
| YYYYMMDD | YYYY-MM-DD | Complete calendar date | 19850412 , 1985-04-12 |
| YYYYWww | YYYY-Www | Week date with accuracy reduced to week | 1985W01 , 1985-W01 |
Time of day
| Basic format | Extended format | Explanation | Example |
| hhmmss | hh:mm:ss | Complete local time | 120101 , 12:01:01 |
| hhmm+hhmm | hh:mm+hh:mm | Local time and the difference from UTC —reduced accuracy | 1201+0000 , 12:01+00:00 |
Datetime
| Basic format | Extended format | Explanation | Example |
| YYYYMMDD[T]hh:mm | YYYY-MM-DD[T]hh:mm | Complete full date and time, T (time) indicator | 19841201T13:00 , 1984-12-01T13:00 |
| YYYYDDDThhmm | YYYY-DDDThh-mm | Complete ordinal date — reduced accuracy time of day | 1984349T1201 , 1984-349T12-01 |
| YYYYMMDDhhmm,m | YYYY-MM-DDhh:mm,m | Complete calendar date — reduced accuracy time of day with one digit decimal fraction for minute — no time designator | 198401011201,m , 1984-01-0112:01,m |
| YYYYMMDDTHH MMSS.mmmZ | YYYY-MM-DDTHH- MM-SS.mmmZ | Four-digit year, two-digit month, two-digit day, T (time) indicator, two-digit hour, two-digit minutes, two-digit seconds in Universal Time | 19840101T120100.000Z , 1984-01-01T12-01-00.000Z |
Created or modified by
Keeping audit logs for individual records always verify these are defined in metadata. Extended format or full UserID/Name is preferred to reduce confusion and is the most human-readable.
| Basic format | Extended format |
| ####### | John Doe |
Active or Deleted
Extended format is preferred to reduce confusion and is the most human-readable.
| Basic format | Extended format |
| T | True |
Status
Record status should be clear with a predefined selection.
| Basic format | Extended format |
| A | Active |
Comments
Any free-form string field should have limitations on the number of characters and type of characters defined by the system in use.
| Basic format | Extended format |
| Alphanumeric | Include special characters |
Location (coordinates and addresses)
To provide consistency with mapping functions and display in use.
| Name | Description | Data Type | Restrictions | Valid Values |
| Full_Address | Complete address formatted by [House_No] + [Direction] + [Street_Name] + [Suffix]. | Text | Do not abbreviate the spelling of street | 349 S 200 E 325 N TERMINAL DR |
| House_No | House Number of address | Text | No abbreviations, ex: 13 TH S | 1300 S |
| Direction | Direction of address | Text | Do not spell out direction, ex: 1300 EAST | 1300 E |
| Street_Name | Street Name of address | Text | Do not abbreviate the spelling of the street | WRIGHT BROTHERS DR |
| Suffix | Suffix/ Street Type | Text | DR ST AVE BLD | |
| Unit | Unit or Suite of Address | Text | Do not include it in Full_Address column, always put it in a separate column | |
| Lat | Latitude | Integer | ||
| Lon | Longitude | Integer | ||
| X_Coord | X Coordinate | Integer | If exported from CAD, must include Coordinate System | |
| Y_Coord | Y Coordinate | Integer | If exported from CAD, must include Coordinate System |
GIS Frequently Asked Questions
Reference
Date time: https://en.wikipedia.org/wiki/ISO_8601