Database Basics for Web Designers

cover

Almost every dynamic website stores its content in a database: posts, products, users, orders and form submissions. You do not need to be a database administrator to build good websites, but understanding the basics helps you design better forms, debug "missing" content, talk to developers and avoid the mistakes that cause slow sites and data loss. This guide covers the database knowledge a web designer actually uses.

What a database is, in website terms

A relational database such as MySQL, MariaDB or PostgreSQL stores data in tables. Each table holds one kind of thing, like posts or customers. Each row is one item, and each column is one property, like a title or an email address.

Tables are connected through keys. A posts table might have an author_id column that points to a row in the users table. That connection is what lets a page show "Written by Dana" under an article without copying Dana's details into every post.

Where you meet databases as a designer

  • A CMS. WordPress stores posts, pages, settings and plugin data in MySQL or MariaDB. Custom fields end up in database tables too.
  • Forms. Contact, booking and registration forms usually save entries in a table.
  • Online stores. Products, variations, stock, orders and customers are all rows in tables.
  • Migrations. Moving a site to a new host means moving its database, not only its files.
  • Debugging. "The page shows the old price" or "the form entry disappeared" are often database questions.

The SQL you will actually use

SQL is the language for asking a database questions. Four commands cover most of what a designer needs to understand:

-- Read rows
SELECT title, published_at
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 10;

-- Add a row
INSERT INTO subscribers (email, created_at)
VALUES ('reader@example.com', NOW());

-- Change rows
UPDATE products SET price = 49 WHERE id = 12;

-- Remove rows
DELETE FROM sessions WHERE expires_at < NOW();

Always be careful with UPDATE and DELETE: without a WHERE clause they change every row in the table. Take a backup first, and test the WHERE condition with a SELECT before running the change.

Joining tables

SELECT posts.title, users.display_name
FROM posts
JOIN users ON users.id = posts.author_id
WHERE posts.status = 'published';

A join combines rows from related tables. When a page shows data from several places, such as a product with its category and reviews, a join is usually behind it.

Empty values

Missing values are stored as NULL, which behaves differently from an empty string or zero. Our guide to COALESCE, ISNULL, IFNULL and NVL explains how to handle them in each database.

Designing data with forms in mind

Form design and database design affect each other. A few habits prevent a lot of trouble later:

  • Split data that people will search or sort by. Store first name, last name, city and postal code in separate fields, not one big text box.
  • Use the right type. Dates as dates, numbers as numbers, yes/no as a boolean. A price stored as text cannot be sorted correctly.
  • Use fixed options where possible. A dropdown of countries produces cleaner data than free text.
  • Collect only what you need. Every extra personal field is something you must protect and may have to delete on request.
  • Plan for growth. If a product may have several images or categories, they belong in a separate related table, not in columns like image1, image2, image3.

Tools for looking at a database

  • phpMyAdmin, available in most hosting control panels, for browsing tables and running queries.
  • Adminer, a lighter single-file alternative.
  • Desktop clients such as TablePlus, DBeaver or HeidiSQL, which connect over SSH to remote servers.
  • WP-CLI for WordPress, which handles exports, imports and search-and-replace safely.

For a local setup to practise on, see How to Run PHP and MySQL Locally.

Security essentials

  • Never build queries by joining user input into SQL. Use prepared statements. SQL Injection: What It Is and How to Prevent It shows why.
  • One database user per site, with access only to that site's database.
  • Strong passwords, stored outside the public web folder.
  • No public database access. The database port should not be open to the internet.
  • Hash user passwords in the application; never store them as plain text.

Backups and migrations

Files can often be recreated from a theme or Git. Database content usually cannot. Back it up daily, keep copies off the server, and test restoring them. When moving a site:

  1. Export the database with mysqldump, WP-CLI or phpMyAdmin.
  2. Create a new database and user on the new host.
  3. Import the export and update the connection details in the site's configuration.
  4. Replace old URLs with a tool that understands serialized data, such as wp search-replace.
  5. Check that the character set is utf8mb4, so emoji and non-Latin text are stored correctly.

The server side of backups is covered in Basic Server Setup for a Small Website.

Why database sites get slow

  • Missing indexes. Searching a large table on a column without an index forces the database to read every row.
  • Too many queries per page. A plugin or theme that runs hundreds of queries on every page load adds up quickly.
  • Bloated tables. Old revisions, expired temporary data and log tables grow over time.
  • No caching. Pages that look the same for every visitor can be cached instead of rebuilt from the database each time.

On WordPress, the Query Monitor plugin shows which queries run and how long they take. More WordPress fixes are in the WordPress troubleshooting checklist.

Where to go next

If you want to go further, learn PHP and SQL together with the resources in The Best Resources to Learn PHP and MySQL, and read PHP for Web Designers in 2026 for how the two fit together on a real site.

Comments