DEV Community

corpdigest
corpdigest

Posted on

Your companies table is lying to you about when things happened

#database #sql #datascience

Every company dataset I have worked with stores history as scalar columns:

founded_year   INT
ceo_name       TEXT
headquarters   TEXT
revenue        BIGINT
Enter fullscreen mode Exit fullscreen mode

Every one of those is a point-in-time fact pretending to be a permanent one. The moment you need to answer "who was in charge when this happened", the schema has already thrown the answer away.

The failure is invisible until you query across time

Take Intel. Founded 18 July 1968 by Gordon Moore and Robert Noyce in Mountain View. If your row says founded_year = 1968 and ceo_name = <whoever it is today>, you have compressed nearly six decades into two fields that were never simultaneously true.

Now try to answer:

  • Who was running the company when the 4004 shipped in 1971?
  • What was the headquarters when the IBM PC design win landed in 1981?
  • Which leadership team owned the process-node slippage that Apple Silicon exposed in 2020?

None of these are answerable. Not because the data is missing, but because the schema has no time axis.

The fix is an event table, not more nullable columns 

create table company_event (
  company_id    bigint not null,
  event_type    text   not null,  -- 'ceo_start' | 'hq_move' | 'rename' | 'launch'
  valid_from    date   not null,
  valid_to      date,             -- null means still current
  payload       jsonb  not null,
  source_url    text   not null,
  primary key (company_id, event_type, valid_from)
);
Enter fullscreen mode Exit fullscreen mode

Three things this buys you.

valid_to IS NULL means current. Your "current CEO" lookup becomes a view rather than a column somebody has to remember to update. Staleness stops being a silent failure and becomes a missing row you can detect.

source_url sits on the row, not the parent. When a specific figure gets challenged you can point at the specific filing that supports that specific claim. A single source column on the company row cannot do that.

Corrections stop being destructive. Updating a scalar ceo_name destroys the previous value. Inserting an event with a new valid_from preserves it, which means you can reconstruct what you believed and when.

The part that actually bit me

The schema is the easy half. The hard half is that most sources do not give you a clean valid_from. Press releases say "effective immediately". Filings say "during fiscal 2024". Wikipedia says "1968". So you need to store how precise the date is:

valid_from            date
valid_from_precision  text  -- 'day' | 'month' | 'quarter' | 'year'
Enter fullscreen mode Exit fullscreen mode

Without that column you will silently invent a day-level date no source supports, and something downstream will treat it as exact. I have done this. It is very annoying to unpick later.

Worked example

If you want to see the shape rendered as narrative rather than rows, the Intel company history page is built this way -- founding, the 4004, the IBM PC standardisation and the Apple Silicon transition as discrete dated events with sources attached, rather than one prose blob with a founded_year bolted on.

The rule I have settled on: if a fact about a company can change, it is an event with a start date and a source. It is not a column.

Top comments (0)