Mark

Posted on Apr 24

The Five Most Important Columns to Add to Every Database Table

#webdev #beginners #database #sql

Let me tell you about one of the worst days of my career.

A client called me, furious. Corrupted records, no idea who changed what, no idea when anything was changed — and someone had deleted three years of customer data. Gone. Poof. Like it never existed.

All of that pain could have been avoided with five columns.

Five. Columns.

I have been working in the IT industry for over 25 years — across software development, architecture, and consulting. I have seen systems built brilliantly and systems built badly. And the badly-built ones almost always share the same DNA: nobody thought about auditability until it was too late.

This article is the advice I give to every junior developer I mentor. These aren't opinions. They are hard-won lessons from over two decades of real production systems, angry clients, and 11pm phone calls that should never have happened.

The Five Columns

Here is a quick summary before we dive in:

Column	Type	Purpose
`CreatedBy`	VARCHAR / FK	Who created the record
`LastModifiedBy`	VARCHAR / FK	Who last changed the record
`CreatedDateTime`	DATETIME (UTC)	When the record was created
`LastModifiedDateTime`	DATETIME (UTC)	When the record was last changed
`Deleted`	BOOLEAN	Soft delete flag

Add these to every table. Not most tables. Every table. Let's look at each one.

1. CreatedBy — Own Every Record

CreatedBy is a string column — or a foreign key to your users table if you're being thorough — that stores the name or ID of whoever created the record.

"I'll just remember who created it."

You won't. Future You has the memory of a goldfish and the patience of someone who's been on hold for 45 minutes.

Six months from now, a manager walks up: "Who added this vendor? They've been overcharging us for months." Without CreatedBy, your answer is a shrug. With it, you point to the column, you point to the name, you're the hero. You go home on time.

The rule: Set it on INSERT. Never change it. Ever.

-- Example: Setting CreatedBy on insert
INSERT INTO orders (product_id, quantity, CreatedBy, CreatedDateTime)
VALUES (42, 10, 'jane.smith@company.com', GETUTCDATE());

2. LastModifiedBy — Follow the Trail

Same concept as CreatedBy — name or user ID — but this tracks who last touched the record.

This column earns its keep during debugging. Something's wrong — the price is off, the status makes no sense, the address looks like someone sneezed on the keyboard. You check LastModifiedBy. You have a name. You have a conversation to have.

One important distinction: this is not full audit logging. If you need a complete change history, that's a separate audit table. LastModifiedBy is simply a sticky note on the fridge — quick, lightweight, and far better than nothing.

The rule: Update it every time a record changes. Every. Time.

-- Example: Updating LastModifiedBy on every update
UPDATE orders
SET    quantity         = 20,
       LastModifiedBy   = 'john.doe@company.com',
       LastModifiedDateTime = GETUTCDATE()
WHERE  order_id = 1001;

3. CreatedDateTime — The Birth Certificate of Your Data

The timestamp of when the record entered the world.

Store it in UTC. I cannot stress this enough — UTC.

I once worked on a system that stored timestamps in local time across three time zones. The chaos looked like a conspiracy theory board with red string connecting everything. Don't be that system.

CreatedDateTime answers: When did this order come in? When did this user sign up? When did someone first log this issue?

It is the birth certificate of your data. Set it on INSERT, never touch it again. Immutable. Sacred. Like the last slice of pizza at a team meeting.

The rule: Set on INSERT. UTC only. Never update it.

-- ✅ Correct — UTC
CreatedDateTime DATETIME DEFAULT GETUTCDATE()

-- ❌ Wrong — local time is a trap
CreatedDateTime DATETIME DEFAULT GETDATE() -- timezone-dependent

4. LastModifiedDateTime — Your Sync Superpower

The timestamp of the most recent update. UTC. Always.

This one is particularly powerful for syncing. Building an API or mobile app? You can now ask:

"Give me everything changed since this timestamp."

Boom. Efficient, clean, no need to pull the entire dataset on every request. Your mobile app will thank you. Your server bills will thank you.

It also surfaces anomalies. A record created last year, last modified 30 seconds ago? Worth investigating. Your data is telling you a story — this column helps you read it.

Most databases and ORMs can populate this automatically. There's genuinely no excuse not to have it.

-- Entity Framework Core example (C#)
public class BaseEntity
{
    public string CreatedBy { get; set; }
    public string LastModifiedBy { get; set; }
    public DateTime CreatedDateTime { get; set; }
    public DateTime LastModifiedDateTime { get; set; }
    public bool Deleted { get; set; }
}

With a base entity like this in your ORM, all five columns come along for the ride on every table — automatically.

5. Deleted — The Drawer, Not the Fire

And now my personal favourite: Deleted. A boolean. true or false. 1 or 0.

This is the soft delete pattern — and if you take nothing else from this article, take this:

Never permanently delete important data.

When a user "deletes" something, you set Deleted = true. The record stays in the database. It just disappears from your application's view. That's it.

Why?

Because deletion is almost always a mistake or a change of heart:

A customer removes their account, then calls support three days later wanting it back
An employee wipes a product listing by accident
A developer runs a DELETE query without a WHERE clause — and yes, that happens more than anyone admits publicly

With soft deletes, none of that is permanent. You are not throwing data in a fire — you are putting it in a drawer.

-- Soft delete — the right way
UPDATE customers
SET    Deleted           = 1,
       LastModifiedBy    = 'system',
       LastModifiedDateTime = GETUTCDATE()
WHERE  customer_id = 9001;

-- Query always filters deleted records
SELECT *
FROM   customers
WHERE  Deleted = 0;

One non-negotiable: filter every query with WHERE Deleted = 0 (or false), or your "deleted" records will haunt every screen in your application. Most modern ORMs handle this via global query filters — use that feature.

// Entity Framework Core — global query filter
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .HasQueryFilter(c => !c.Deleted);
}

Now every query against Customer automatically excludes deleted records. No WHERE clause required. No chance of forgetting.

Putting It All Together

Here is a complete SQL table definition with all five columns baked in as a standard template:

CREATE TABLE customers
(
    -- Business columns
    customer_id          INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    first_name           VARCHAR(100)   NOT NULL,
    last_name            VARCHAR(100)   NOT NULL,
    email                VARCHAR(255)   NOT NULL,

    -- Audit columns (add these to EVERY table)
    CreatedBy            VARCHAR(255)   NOT NULL,
    LastModifiedBy       VARCHAR(255)   NOT NULL,
    CreatedDateTime      DATETIME       NOT NULL DEFAULT GETUTCDATE(),
    LastModifiedDateTime DATETIME       NOT NULL DEFAULT GETUTCDATE(),
    Deleted              BIT            NOT NULL DEFAULT 0
);

Clean. Simple. And you'll never have that 11pm phone call.

The Summary

Column	Set on	Updated on	Notes
`CreatedBy`	INSERT	Never	Immutable — who created it
`LastModifiedBy`	INSERT	Every UPDATE	Who last touched it
`CreatedDateTime`	INSERT	Never	UTC only. Immutable
`LastModifiedDateTime`	INSERT	Every UPDATE	UTC only. Powers syncing
`Deleted`	INSERT (default: false)	On soft delete	Never hard delete important data

Final Thought

Add these to every table. Lock them into your base model or migration templates so they appear by default. Make it impossible not to have them.

I have been in this industry for 25 years. I have seen what happens when these columns exist and what happens when they don't.

The difference is not academic. It is the difference between a recoverable situation and a very bad day.

Your future self — the one getting called at 11pm because something broke — will thank you.

Have you been burned by not having one of these columns? Drop it in the comments — I guarantee you're not alone.

If you found this useful, follow me for more no-nonsense, senior developer advice aimed at developers who want to grow faster.

DEV Community