Designing tables for your bot

Before you write a single query, you decide what your tables look like — the columns, the keys, how one table relates to another. Get the shape right and everything downstream (leaderboards, settings, upserts) falls out easily. Get it wrong and you fight your own schema forever. The good news: almost every bot needs the same four shapes, and this guide gives you all four.

The examples use SQLite (better-sqlite3, the bot default) but the shapes are identical in the managed MySQL and PostgreSQL databases — only the column-type spellings differ, and this guide flags those.

Rule zero: Discord IDs are text

Every user, guild, channel, and message on Discord has a snowflake — a big numeric ID like 739283749283749283. Store it as TEXT (SQLite) or VARCHAR(20) (MySQL/PostgreSQL), never as a plain number.

⚠️ Heads up: Snowflakes are 64-bit, but JavaScript numbers lose precision above about 9 quadrillion — paste one into a number column and the last few digits quietly change. Text stores the ID exactly, and you never do arithmetic on an ID anyway, so nothing is lost.

That is why every verified recipe here — leveling, economy, migrations — uses TEXT for user_id and guild_id. Follow the same rule and your data lines up with theirs.

The four shapes

Match your data to one of these and the primary key writes itself:

Per-user

When a fact belongs to a person no matter which server they're in, the user's ID is the key. This is the economy recipe's wallet:

CREATE TABLE wallets (
  user_id    TEXT    PRIMARY KEY,
  balance    INTEGER NOT NULL DEFAULT 0,
  last_daily INTEGER NOT NULL DEFAULT 0
);

Per-guild

Settings belong to a server, so the guild ID is the key. One row holds that server's configuration:

CREATE TABLE guild_settings (
  guild_id TEXT PRIMARY KEY,
  prefix   TEXT NOT NULL DEFAULT '!',
  log_channel TEXT
);

Per-guild-per-user

XP is earned per server — the same person has separate progress in each guild. That needs both IDs in the key, together, as a composite primary key. This is the leveling recipe:

CREATE TABLE levels (
  guild_id TEXT    NOT NULL,
  user_id  TEXT    NOT NULL,
  xp       INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (guild_id, user_id)
);

The composite key means one row per (guild, user) pair, and every query filters by guild_id so servers never bleed into each other.

Event logs (one-to-many)

When a user can have many of something — several warnings, a purchase history, a moderation trail — one row per user won't fit. Give the log its own table with its own auto-incrementing id, and store the user_id as an ordinary column:

CREATE TABLE warnings (
  id      INTEGER PRIMARY KEY,   -- auto-assigned; AUTO_INCREMENT in MySQL
  user_id TEXT    NOT NULL,
  guild_id TEXT   NOT NULL,
  reason  TEXT,
  created INTEGER NOT NULL       -- a timestamp
);

Now one user maps to many warning rows — the classic one-to-many relationship. You fetch a user's warnings with WHERE user_id = ? and join back to other tables when you need names or settings (see JOINs in the SQL guide).

Three habits that save you later

• NOT NULL DEFAULT, always. Giving balance, xp, and friends a default of 0 lets a first-seen user's row be created by a plain upsert with no special case — exactly what makes INSERT ... ON CONFLICT in the recipes "just work" on the first message and the thousandth.

• Money is an INTEGER. Store whole coins, cents, or points — never a floating-point number. 0.1 + 0.2 isn't 0.3 in floats, and that drift is a bug an economy can't afford. The economy recipe counts whole coins for exactly this reason.

• Index what you sort or filter by. The primary key is already indexed. A leaderboard that runs ORDER BY xp DESC on a big table benefits from an extra index so it doesn't scan everyone:

  CREATE INDEX idx_levels_xp ON levels (guild_id, xp);

When to split into a second table

The deciding question is: is it one value, or a list?

• One value per user — a balance, a prefix, a level — stays a column. Don't overthink it.
• A list per user — items owned, warnings, past purchases — becomes its own table with a user_id column, one row per item. The economy recipe notes this exact move: a shop that grants items adds an inventory table rather than cramming a list into one field.

Resist the temptation to store a list as a comma-joined string or a JSON blob in one column. The moment you want to ask "who owns a Crown?" or "how many warnings this month?", a real row per item answers it with one query; a packed string forces you to load and parse everything. That's what "normalize" means in practice — give repeating things their own rows.

🎯 Good to know: Keep it flat until a list forces your hand. Over-splitting a simple bot into a dozen tables is its own kind of pain. Start with the four shapes above; add tables when the data is genuinely a list.

MySQL and PostgreSQL spellings

The shapes don't change on a managed database — a few type names do:

Everything else — composite keys, NOT NULL DEFAULT, indexes, one-to-many tables — is written the same way. Pick your engine with Choosing between Redis, SQLite, and MySQL; the shapes above travel with you.

Next steps

• Just enough SQL
• Evolving schemas without losing data
• A leveling system with SQLite
• Connect your app to a database