Design and manage an optimized database

The database decides whether a programmatic site is worth indexing. The template only displays what you stored, so if two rows differ by nothing but a city name, no design will save those two pages. Work out what makes each page useful, model that, and choose the tool last.

For this chapter, say you are building a directory of coworking spaces across 2,000 French cities, with one page per city.

Start from the page and work back to the columns

Take your page zoning and ask where the content of each zone comes from. There are only three possible answers: a stored fact, something computed from stored facts, or generated text.

Zone on the page	Field	Origin
Title tag and H1	`city.name`, number of spaces	Computed
Intro paragraph	`city.intro_text`	Generated, then reviewed
List of spaces	`space.name`, `space.day_pass_eur`	Stored
Price summary	Lowest and median day pass	Computed
Nearby cities	`city_link`	Stored relation
JSON-LD	`space.name`, `space.address`	Computed

The computed rows are the ones I push hardest. “The cheapest day pass in this city is X, and most spaces with meeting rooms charge Y” is information a page can only carry if the data exists. It also separates one page from the next far better than any rewritten paragraph.

Go granular. On Deux.io I built more than 90 variables, each one an element that makes a page unique: images, titles, metadata, schemas, highlighted profiles, value propositions, featured articles, links. Depending on the variable, that meant anywhere from 100 to over 2,500 variations.

Model the things that exist, then the pages

The classic mistake is one giant sheet with one row per page, columns named link_1 to link_5, and the same price pasted in three places. It holds for 50 pages. After that, every correction becomes a search and replace.

Give each real thing its own table, store relations explicitly, and keep the SEO state of a page separate from the facts behind it.

CREATE TABLE city (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug        TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  region      TEXT NOT NULL,
  intro_text  TEXT
);

CREATE TABLE space (
  id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_id        TEXT NOT NULL UNIQUE,
  city_id            BIGINT NOT NULL REFERENCES city (id),
  name               TEXT NOT NULL,
  address            TEXT NOT NULL,
  day_pass_eur       NUMERIC(6, 2),
  has_meeting_rooms  BOOLEAN NOT NULL DEFAULT false,
  synced_at          DATE NOT NULL
);

CREATE TABLE city_link (
  from_city_id  BIGINT NOT NULL REFERENCES city (id),
  to_city_id    BIGINT NOT NULL REFERENCES city (id),
  PRIMARY KEY (from_city_id, to_city_id)
);

CREATE TABLE page (
  id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  city_id           BIGINT NOT NULL UNIQUE REFERENCES city (id),
  path              TEXT NOT NULL UNIQUE,
  template          TEXT NOT NULL,
  title             TEXT NOT NULL,
  meta_description  TEXT NOT NULL,
  status            TEXT NOT NULL DEFAULT 'draft'
                    CHECK (status IN ('draft', 'ready', 'published', 'pruned')),
  wave              SMALLINT,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

The page table does more work than it seems to. status controls what gets published or withdrawn, wave drives a staged rollout, updated_at feeds the sitemap, and template lets you read results by group later. city_link is what your internal linking will be generated from, so a nearby city is a decision you can inspect and correct.

You can build exactly the same thing in a spreadsheet: one tab per table, stable IDs, lookups instead of pasted values.

Decide which rows deserve a page

Your database should be able to refuse a page. Google’s scaled content abuse policy targets mass-produced pages with little value, whatever method produced them, and a city with one coworking space and a generated paragraph is that kind of page. AI answers have no use for it either, since it holds nothing worth citing.

Write the rule down and make it a query. The threshold below is an example, set your own according to what a useful page needs.

SELECT c.slug, COUNT(s.id) AS spaces
FROM city AS c
LEFT JOIN space AS s ON s.city_id = c.id
GROUP BY c.slug
HAVING COUNT(s.id) < 3;

Those cities stay in draft. Their spaces can still appear on a regional page, where they add something.

Pick the tool your team will still maintain in a year

Tool	Where it fits	Where it hurts
Google Sheets	Fast start, formulas everyone can read, easy to connect to Make or Zapier	Slows down on large sheets, weak on permissions and validation
Airtable	Real relations and views without code	Record limits and pricing depend on the plan
PostgreSQL, MySQL	Constraints, joins, large volumes, clean audit queries	Needs someone comfortable with SQL and hosting
MongoDB, Firebase	Records whose shape really varies, data already living there	Programmatic data is usually relational, so you end up rebuilding joins

I start in Google Sheets and stay there until it hurts. Both Derrick and Deux.io ran on Google Sheets feeding Webflow. If you publish through a CMS, keep the sheet or database as the source of truth and treat the CMS as a publishing target, and check your plan’s item limit before you commit. On a static stack like Astro, the database is simply read at build time.

Serving matters too. Pages should come from static files or a CDN cache so that a crawl never queries your database for every URL.

Automate imports without overwriting your work

Data will arrive from external APIs, scrapers, a CRM or a partner’s export, moved by Python, Make or Zapier. Three habits keep that safe.

Key every imported record on a stable external ID and update it in place. Deleting and reloading a table changes your IDs and breaks every relation.

INSERT INTO space (external_id, city_id, name, address, day_pass_eur, synced_at)
VALUES ($1, $2, $3, $4, $5, CURRENT_DATE)
ON CONFLICT (external_id) DO UPDATE
SET name         = EXCLUDED.name,
    address      = EXCLUDED.address,
    day_pass_eur = EXCLUDED.day_pass_eur,
    synced_at    = EXCLUDED.synced_at;

Never let an import write to a column a human edits. Corrected names and reviewed texts belong in fields the sync doesn’t touch.

Match the refresh rate to how fast the data really changes. Coworking prices justify a weekly job. Hourly syncs are extra failure points unless your data moves by the hour.

Keep generated text in its own columns

Titles, meta descriptions and headings are usually better built from formulas on your fields than written by a model, because they stay accurate when the data changes. Where you do use AI, for an intro paragraph for example, store the output in its own column with a review status, feed it only facts from your tables, and run a duplicate check before anything goes out. Deux.io’s stack combined AI with an AI content checker and a duplicate content checker. Derrick was built with no AI at all, which is a good reminder that the data carries the page.

Audit the data before it becomes a page

These checks should run automatically, and any failure should keep the row out of ready:

required fields are empty or contain placeholder values
titles or meta descriptions are duplicated
records are duplicated under slightly different names
synced_at is older than your freshness rule
numbers fall outside a plausible range, such as a day pass of 0 euros

At high volume, send these results to BigQuery and a Looker Studio dashboard, so a broken import shows up as a spike and doesn’t need someone to spot it.

Then connect data to outcomes. Because each page row knows its template, its wave and how much data it holds, you can test whether pages with more listings really perform better. I cover that join in tracking performance, alongside Search Console and crawl data from Screaming Frog or Ahrefs.

Your next step: write the zone, field and origin table for one template, then run your threshold rule over your real data. The number of rows that survive is the honest size of your project, and those are the pages to take into production and indexing.