How to Design a User Table

The user table is often the first table in a product. Many first versions put every user-related field into it: email, nickname, avatar, password, phone, plan, credits, login count, referrer, preferences, company information, permissions, and last visit time.

This feels convenient in the short term, but over time the table becomes a bloated catch-all. The user table should not hold every piece of user-related data. It should reliably hold the core identity, state, and basic profile. Fast-changing business attributes should move to better tables.

The key is separating what describes “who the user is,” what the user “owns,” what the user “has done,” and what the user “prefers.”

The Core Responsibility of the User Table

The core responsibility of the user table is identifying a user. It should answer: who is this user, how can we contact them, when were they created, and is the account usable?

That means a first version usually needs only stable fields: user ID, email, name, avatar, auth source, account status, created time, and updated time. Password hash can live in the user table or a separate auth table depending on your authentication design.

Do not put orders, subscriptions, permissions, usage, preferences, and behavior logs all into the user table. They are related to the user, but they do not necessarily belong there.

The more stable the user table is, the easier the system is to extend. It should be the identity foundation, not a business junk drawer.

Email Is Usually the Most Stable Primary Identifier

For indie builders and global SaaS, email is usually the most stable primary identifier. It works across devices, supports notifications, maps well to payment systems, and helps merge social login accounts.

The user table can include an email field and enforce uniqueness on a normalized email. Normalization usually means lowercasing and trimming whitespace. Do not treat different casing of the same email as different accounts.

If you support Google, GitHub, or Apple login, do not use the provider ID as the user primary key. A better approach is giving the user table its own id and storing external identities in user_identities or a similar table. Then one user can bind multiple login methods.

Phone number can also be an identifier, but for global products it introduces country codes, SMS cost, compliance, and delivery issues. Unless the product strongly depends on phone numbers, it does not need to be the primary account in the first version.

Do Not Put Plans and Usage Directly Into Users

Many early products add plan, credits, is_pro, or expired_at to the user table. This is fast at first, but once subscriptions, refunds, teams, gifted credits, and usage records appear, it becomes messy.

A better approach is separating subscriptions and usage. The user table stores identity and basic state. Plan data lives in subscriptions or user_plans. Usage lives in usage_records or credit_accounts.

If your product is extremely simple and only has free and paid tiers, a cached field on the user table can be acceptable temporarily. But treat it as redundant state, not the source of truth. The real payment and subscription state should come from order or subscription tables.

The user table should not become the billing system. Who the user is and what the user bought are different questions.

Account Status Must Be Clear

The user table should have an account status field such as active, pending, disabled, or deleted. Avoid representing status through multiple booleans like is_active, is_deleted, and is_banned together. These combinations easily become contradictory.

Each status should have clear meaning. pending may mean email is not verified. active means normal use is allowed. disabled means banned or manually disabled. deleted means account deletion or soft deletion.

If you need to record ban reason, deletion time, or email verification time, add fields such as disabled_reason, deleted_at, and email_verified_at. The main status describes the current state. Supporting fields describe reason and time.

Account status affects login, permissions, data display, and admin actions, so the backend should judge it consistently.

Split Profile and Settings When Needed

Name, avatar, and display name can live in the user table because they are basic profile fields, do not change much, and are often used. More complex profile data such as company, role, website, bio, and social links can live in user_profiles.

User settings should usually be separate. Language, timezone, notification preferences, theme, default export format, and email subscription switches are preference data. They change often and may grow as features increase.

You can create a user_settings table, or store preferences as JSON in the early stage. The key is avoiding endless growth in the user table.

A practical rule: fields needed frequently for login and authorization can stay in users. Display-only or preference fields should usually move out.

Authentication Identities Should Be Separate

If you support multiple login methods, create a separate identity table:

user_identities
- id
- user_id
- provider
- provider_user_id
- email
- created_at

This lets users bind email/password, Google, GitHub, and Apple at the same time. The user table represents the product-level user. The identity table represents external login sources.

This design also supports account merging. If two login methods belong to the same user, bind both to the same user_id. If you add another login method later, the user table does not need to change.

If the first version only has email login, you can delay this split. But if social login is planned, creating an identity table early is cleaner.

A Recommended User Table Structure

A first user table can look like this:

users
- id
- email
- email_normalized
- name
- avatar_url
- status
- email_verified_at
- last_login_at
- created_at
- updated_at
- deleted_at

Supporting tables can be:

user_identities
- id, user_id, provider, provider_user_id, email, created_at

user_profiles
- user_id, company, role, website, bio, created_at, updated_at

user_settings
- user_id, locale, timezone, notification_json, preference_json, updated_at

subscriptions
- id, user_id, plan, status, provider, current_period_end

usage_records
- id, user_id, feature, amount, period, created_at

You do not need to create all of these tables at once, but understand their responsibilities. Users handles identity. Identities handles login methods. Profiles handles display information. Settings handles preferences. Subscriptions and usage handle benefits.

Summary

The user table is not a universal table. It should reliably carry user identity, basic profile, and account status, not every business detail. Email is usually the most stable primary identifier. Plans, usage, settings, and behavior logs should be split carefully.

The clearer the first user table is, the easier login, payment, permissions, admin, and teams become later. Do not save one table today by pushing all future complexity into users.

Homework

• Check whether your user table contains orders, usage, permissions, or preference fields.
• Mark which fields are required for identity and which are only business attributes.
• Design a status field and define each state.
• If you support social login, design a user_identities table.
• Move plan and usage out of the user table into separate structures.

Next Lesson

Should You Build Payment Early or Last: payment is not just adding a button. It connects pricing, permissions, orders, delivery, and support.

https://en.harries.blog/how-to-design-a-user-table/