Database name convention: DATETIME column

Question

What is your naming convention for DATETIME columns (in my case, using MS SQL Server)

For a column that stores when the row was created CreatedDatetime makes sense, or LastModifiedDatetime.

But for a simple table, let's say one called Event, would you create columns called:

EventID,                 // Primary key
EventDatetime,           // When the event is happening
EventEnabled             // Is the event is on

or

ID,                      // Primary key
Datetime,                // When the event is happening
Enabled                  // Is the event is on

If you'd use neither convention: Please provide the column name you would use.

Answer 1

I normally name DATETIME columns as ACTION_WORD_on: created_on, completed_on, etc.

The ACTION_WORD defines what the column represents, and the suffix (_on) indicates that the column represents time.

Other suffixes (or even prefixes) may be used to specify the data type (_at, _UTC, when_, etc).

Be descriptive. Be consistent.

Answer 2

Maybe that's just me, but I don't believe you should name your columns with data types, neither replicate the table name all over the fields.

Answer 3

I prefer to create columns in the second form--although I'd probably want a more descriptive name than Datetime, depending on what its use would be.

Edit: In this sort of situation, I might actually go with a hybrid for that single field, and make it 'EventDate', 'StartDate', or something similar.

Answer 4

The name should communicate what Business meaning of the data is in the column... "DateTime" is just the Type of the data. Is it when the event happened? when it was recorded? when it was stored in the DB? When the data was last modified?

If it efficiently communicates the meaning of what the column contains, the name is fine. "DateTime" is not fine. "EventDateTime" is only very slightly better. If the table holds events, then any datetime field in the table is an EventDateTime (It records some datetime related to the event). Although if there's only one datetime column in an "Events" table, then EventDateTime implies that it's when the event happened, so that's probably ok.

Choose or select the name so it communicates the meaning of the value...

Given edited question, some suggested names might be:

Occurred, or OccurredDateTime, or OccurredUTC, (or OccurredLocal), or, if events in your business model have duration, then perhaps StartedUtc, or BeganUtc, or InitiatedUtc, etc.

Answer 5

I would avoid using datatypes for column names (a DATETIME column called Datetime), so I vote for the first option.

Answer 6

Why call it EventDateTime, when you don't also use EventIDInt, or EventEnbaledVarchar? Why inlcude the data type in the column name? (My rule of thumb is, if they're accessing data in a table, they better know what the column data types are, 'cause otherwise they don't know what they're working with.)

These days I prefer what I think of as descriptive column names, such as:
CreateDate
DateCreated
CreatedAt
CreatedOn (if there's no time portion)
AddedOn (might be semanitcally more appropriate, depending on the data)

Picking a "label" and using it consistantly in every table that requires that kind data is also a good thing. For example, having a "CreateDate" column in (almost) every table is fine, because then you will always know which column in every table will tell you when a row was created. Don't get hung up with the "but they all have to have unique names" argument; if you're writing a query, you had better know which tables you're pulling each column from.

--Edit--

I just recalled an exception I've done in the past. If a DateTime (or SmallDateTime) column will contain no time portion, just the date, as a "reminder" I'd put "Date" in the column name, such as "BilledDate" instead of "Billed" or "BilledOn". This shouldn't apply when tracking when rows were added, since you'd want the time as well.