Multiplayer Game Backend ER diagram and database schema

What this schema models

A complete multiplayer game backend database design, built the way live-service games protect their economies and ranked ladders: 30 tables, 19 enums and 67 relationships covering player accounts with linked platform identities, a currency wallet ledger, items and inventory, store offers and receipt-validated purchases, seasons, game modes, matchmaking tickets and match results, append-only rating history and leaderboard snapshots, achievements, friends, parties and guilds, and player reports and sanctions. THE FOUR DESIGN RULES 1. A wallet is a ledger, never a balance column. Every credit and debit of every currency is an insert into wallet_ledger_entries with its cause (purchase, match reward, store spend, admin adjustment) and a running balance_after computed under a row lock. There is no balance to UPDATE, so a duplication exploit shows up as rows with a reason, and any balance can be rebuilt from history. 2. Every grant is idempotent. A purchase is unique on (platform, platform transaction id), so a resent receipt returns the existing purchase; every ledger entry carries a unique idempotency key, so a retried fulfilment or reward collides instead of paying twice. 3. Ratings are append-only. rating_events records MMR before and after, per player, per mode, per season; player_ratings is only a cache of the latest row. Undoing the games lost to a banned cheater is a correction row, never an edit. 4. One game account, many platforms. players is independent of any storefront or console network; platform_identities links each external account to exactly one player at a time, which is what makes cross-progression work. Platforms are data, so supporting a new storefront is an INSERT. DETAILS WORTH STEALING - Friendships are stored once as an ordered pair (player_low_id < player_high_id), so A-B and B-A can never both exist. - Leaderboards are snapshots: readers page a frozen board instead of sorting a live table, and the final snapshot is the one season rewards are paid from. - Inventory items record what granted them (purchase, match, achievement), and a refund or chargeback sets revoked_at instead of deleting the row. - Sanctions are rows with a time window (warnings, chat mutes, queue cooldowns, ranked bans, account bans); players.status only caches what is active. - Admin currency adjustments must name a staff account, so an insider grant is always attributed. - Matches record the server build, so every match played on a buggy build can be found and invalidated; an invalidated match pays no rewards and moves no ratings. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: players and identity, social, matchmaking and matches, ratings and progression, economy and store, and moderation. Every table carries a note. Postgres types throughout; the two jsonb columns (matchmaking latency samples and per-match player stats) map to JSON in MySQL or NVARCHAR(MAX) in SQL Server. Useful for developers building the backend for an online multiplayer game, and for students who need a realistic multiplayer game ER diagram for a DBMS project. This is a schema design reference; anti-cheat, rating math and platform store rules belong in your own services.

Tables (30)

Relationships (67)

• achievements.reward_currency_id references currencies.id many to one
• achievements.reward_item_id references items.id many to one
• friendships.player_high_id references players.id many to one
• friendships.player_low_id references players.id many to one
• friendships.requested_by references players.id many to one
• guild_members.guild_id references guilds.id many to one
• guild_members.player_id references players.id many to one
• inventory_items.achievement_id references achievements.id many to one
• inventory_items.item_id references items.id many to one
• inventory_items.match_id references matches.id many to one
• inventory_items.player_id references players.id many to one
• inventory_items.purchase_id references purchases.id many to one
• leaderboard_entries.player_id references players.id many to one
• leaderboard_entries.snapshot_id references leaderboard_snapshots.id many to one
• leaderboard_snapshots.game_mode_id references game_modes.id many to one
• leaderboard_snapshots.season_id references seasons.id many to one
• match_participants.match_id references matches.id many to one
• match_participants.party_id references parties.id many to one
• match_participants.player_id references players.id many to one
• matches.game_mode_id references game_modes.id many to one
• matches.season_id references seasons.id many to one
• matchmaking_tickets.game_mode_id references game_modes.id many to one
• matchmaking_tickets.match_id references matches.id many to one
• matchmaking_tickets.party_id references parties.id many to one
• matchmaking_tickets.player_id references players.id many to one
• offer_contents.currency_id references currencies.id many to one
• offer_contents.item_id references items.id many to one
• offer_contents.store_offer_id references store_offers.id many to one
• parties.game_mode_id references game_modes.id many to one
• parties.leader_id references players.id many to one
• party_members.party_id references parties.id many to one
• party_members.player_id references players.id many to one
• platform_identities.platform_id references platforms.id many to one
• platform_identities.player_id references players.id many to one
• player_achievements.achievement_id references achievements.id many to one
• player_achievements.player_id references players.id many to one
• player_profiles.avatar_item_id references items.id many to one
• player_profiles.banner_item_id references items.id many to one
• player_profiles.title_achievement_id references achievements.id many to one
• player_ratings.game_mode_id references game_modes.id many to one
• player_ratings.last_rating_event_id references rating_events.id many to one
• player_ratings.player_id references players.id many to one
• player_ratings.season_id references seasons.id many to one
• player_reports.match_id references matches.id many to one
• player_reports.reported_player_id references players.id many to one
• player_reports.reporter_id references players.id many to one
• player_reports.reviewed_by references staff_accounts.id many to one
• player_reports.sanction_id references sanctions.id many to one
• players.id references player_profiles.player_id one to one
• purchases.platform_id references platforms.id many to one
• purchases.player_id references players.id many to one
• purchases.store_offer_id references store_offers.id many to one
• rating_events.game_mode_id references game_modes.id many to one
• rating_events.match_id references matches.id many to one
• rating_events.player_id references players.id many to one
• rating_events.season_id references seasons.id many to one
• sanctions.evidence_match_id references matches.id many to one
• sanctions.issued_by references staff_accounts.id many to one
• sanctions.player_id references players.id many to one
• sanctions.revoked_by references staff_accounts.id many to one
• store_offers.price_currency_id references currencies.id many to one
• wallet_ledger_entries.achievement_id references achievements.id many to one
• wallet_ledger_entries.currency_id references currencies.id many to one
• wallet_ledger_entries.match_id references matches.id many to one
• wallet_ledger_entries.player_id references players.id many to one
• wallet_ledger_entries.purchase_id references purchases.id many to one
• wallet_ledger_entries.staff_account_id references staff_accounts.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Multiplayer Game Backend database schema?

30 tables and 67 relationships, across 245 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.