Database Table Design Checklist

There's no answer key for table design. Give the same requirements to three teams and you'll get three schemas. What is predictable is where the trouble shows up: naming, data types, primary keys, nullability, and indexes. Decisions made loosely in those five places come back months later as migration work.

The sections below give decision criteria for naming, keys, data types, NULL policy and indexes rather than a finished schema to copy. A review checklist collects the same checks at the end.

Naming: decide the rules before you need them

Names outlive everything else in a schema. A data type can be altered later; a name has already spread into application code, queries and documents by the time you want to change it.

Table names

Pick singular or plural and stay there. Neither is correct in the abstract; consistency is. Plural (members, posts) is marginally more common, partly because singular names like user collide with reserved words in some systems. What matters is not mixing both in one schema.

Lowercase with underscores. order_items avoids the case-sensitivity differences between platforms. Mixed-case names can behave differently depending on the operating system the server runs on, which turns into a surprise the first time a schema moves.

Prefixes only when they carry information. tb_ on every table tells you nothing. A domain prefix like shop_orders earns its place when several services share one database.

Junction tables take both names, until they mean something. post_tags is fine while it only connects. Once it gains "tagged by" and "tagged at", it isn't a connector any more, and a name that stands on its own — post_taggings — reads better.

Column names

A handful of conventions makes every later decision faster.

• Primary key is id. Inside members, there's no reason to write member_id. It becomes member_id in the tables that reference it, which is exactly where the relationship should be visible.
• Foreign keys are singular_id. member_id, category_id. When one table references another twice, put the role in front: writer_id, approver_id.
• _at for timestamps, _date for dates. created_at DATETIME and birth_date DATE tell you what's inside without opening the definition.
• Booleans start with is_ or has_. is_active beats active, where a value of 1 could plausibly mean either state.
• Put units in the name. price_krw and weight_kg beat price and weight. A unit that lives only in the column description won't be read by whoever writes the next query.

Keep an abbreviation list

qty, amt, cd, dt mean slightly different things in every shop. If you plan to use more than a couple, write them down with their full forms in the specification. It saves the next person from guessing whether reg_dt is a date or a timestamp. What else belongs in that document is covered in How to Write a Table Specification.

Primary keys: surrogate by default, exceptions on purpose

The primary key is the hardest decision to reverse. The cost of changing it grows with every table that references it.

Default to a surrogate key. id BIGINT AUTO_INCREMENT carries no business meaning, so business rules can change without touching it. Key a table on email and the day a member updates their address, every referencing table has to be updated with it.

Natural keys still earn their place. Country and currency codes fixed by international standard, and the (post_id, tag_id) composite in a junction table, are the usual cases. If the value genuinely doesn't change and using it removes a join, natural is simpler.

Decide the INT/BIGINT boundary early. INT tops out around 2.1 billion. A members table will never get there; a log or history table can. Converting INT to BIGINT on a large live table means a long lock, so start wide where growth is fast.

Using UUIDs as primary keys

UUIDs make sense for a stated reason: keys generated across servers before insert, or identifiers where a sequential number would leak volume. If order numbers increment by one, a competitor can estimate your daily volume from two purchases.

Two costs come with them. Storage grows, and randomly generated UUIDs scatter inserts across the whole index. A B-tree is most efficient when values arrive in order; random values touch a different page every time, which hurts both insert throughput and cache hit rate. That's the behavior of UUID version 4, the one most people reach for.

UUID version 7 reduces this problem. Standardized in RFC 9562 (2024), it puts a 48-bit millisecond timestamp in the leading bits; implementations can use the remaining bits for finer time precision, a counter, and randomness. Values are therefore time-ordered and inserts tend to cluster near the right-hand edge of the index instead of scattering like version 4. Generation order within the same millisecond is not automatically guaranteed, however; that depends on the generator. PostgreSQL 18 ships a uuidv7() function, while other environments commonly generate them in an application library.

The other common arrangement is to split the job: numeric surrogate keys for internal joins and foreign keys, a separate UUID column for anything exposed in a URL or API. Performance and exposure get solved by different columns, which is why larger services often end up here.

Choosing data types

Types are awkward to change once data has accumulated. They don't have to be perfect up front, but these rules remove most of the later rework.

Text

Start with VARCHAR and choose the length for a reason. VARCHAR(255) on every name column is a habit inherited from old defaults, not a rule. Email at 255, a display name at 50, a postal code as fixed-width CHAR — let the data decide.

For values with no predictable ceiling, use the TEXT family. MySQL's TEXT stops at 65,535 bytes, which long-form content reaches sooner than people expect; MEDIUMTEXT is the usual answer.

Can you put a UNIQUE index on VARCHAR(255)?

A piece of advice circulates online: "under utf8mb4, anything past 191 characters can't be indexed." That's conditionally true at best, and following it blindly imposes a limit you don't have.

InnoDB's index key prefix limit depends on row format and page size. With the default 16KB page, DYNAMIC or COMPRESSED allows 3,072 bytes; REDUNDANT or COMPACT allows 767. A smaller 8KB or 4KB page lowers the 3,072-byte limit proportionally. Since utf8mb4 uses up to 4 bytes per character, 767 bytes works out to 191 characters — which is where the advice came from.

MySQL has defaulted to DYNAMIC since 5.7.9, so with the default 16KB page, VARCHAR(255) needs at most 1,020 bytes and fits inside 3,072. Under those conditions, a UNIQUE index on an email column is valid. Tables carried over from older servers, or environments with a different row format or page size, can have a lower limit, so check those settings if the index fails.

Type limits worth memorizing

The numbers you end up looking up mid-design, for MySQL:

Type	Range or ceiling	Where it bites
INT	about -2.1B to 2.1B	Log and history tables reach it
INT UNSIGNED	0 to about 4.3B	Only when negatives are impossible
BIGINT	about ±9.2 quintillion	Effectively never a concern
VARCHAR	within a 65,535-byte row limit	Wide tables force shorter lengths
TEXT	65,535 bytes	Long-form content needs MEDIUMTEXT
DATETIME	year 1000 to 9999	Range is rarely the issue
TIMESTAMP	1970 to 2038	The 2038 ceiling is real
DECIMAL	65 digits total, 30 decimal	Precision is not the constraint

Two of these actually bite in practice. A row is capped at 65,535 bytes, so a table with dozens of VARCHAR(255) columns fails at creation. And an InnoDB table maxes out at 1,017 columns — if you're anywhere near that, the signal isn't about types, it's that the table needs splitting.

Character set and collation are design decisions too

Choosing a type and skipping the character set is how data gets mangled later. On MySQL, storing anything beyond the basic multilingual plane — emoji, most notably — requires utf8mb4. The confusingly named utf8 was an alias for utf8mb3, which tops out at three bytes and rejects four-byte characters; utf8mb3 is deprecated as of MySQL 8.0, so new schemas have an easy decision.

Collation is the rule set for comparison and sorting: whether case and accents matter. Names ending in _ci are case-insensitive, _cs case-sensitive. A case-insensitive collation on a UNIQUE email column means [email protected] and [email protected] collide — which is usually what you want. For codes or hashes where case carries meaning, set the collation explicitly.

Character set and collation can be set per database, per table and per column, which makes drift easy. Joining columns with different collations can prevent index use and slow the query down, so it's worth keeping one setting across the schema.

Numbers

Choose between INT and BIGINT on expected row counts. UNSIGNED doubles the positive range, but check first whether any calculation can produce a negative intermediate value.

Money and rates are DECIMAL. FLOAT and DOUBLE are binary floating point and cannot represent 0.1 exactly. One row looks fine; fifty thousand rows summed do not. Write the precision explicitly, as in DECIMAL(15,2), and give tax or exchange rates enough decimal places to survive the arithmetic.

Dates and times

DATE holds a day, DATETIME and TIMESTAMP hold a moment. Storing a birthday in a DATETIME invites off-by-midnight comparison bugs.

Settle the time zone policy first. Even a single-country service runs into this once servers sit in more than one region or the product expands. Storing UTC and converting on display is the easiest version to reason about. MySQL's TIMESTAMP converts by session time zone and has a 2038 ceiling, so pick it knowingly rather than by habit.

Booleans and status

MySQL has no dedicated boolean type; TINYINT(1) fills in. The real question is whether the value has exactly two states. The moment "approved" becomes approved, rejected and pending, a boolean can't hold it.

ENUM is convenient for status columns but requires a schema change to add a value. When statuses are likely to grow, or when each status needs a label, a sort order and an active flag, a code table you reference scales better.

JSON columns

JSON fits data written whole and read whole: user preferences, raw third-party payloads, anything whose shape changes often and is never a search target.

If you need to filter or join on what's inside, pull it out into columns. Tags in a JSON array inherit exactly the problems of a comma-joined string column. The reasoning is worked through with examples in the first normal form section of Database Normalization.

NULL policy: absent is not the same as empty

NULL is the normal way to record that a value doesn't exist yet. Trouble starts when NULL, empty string and zero are used interchangeably. A phone column holding NULL in some rows and '' in others needs two conditions in every query that touches it.

Default to NOT NULL. Where a value must exist, block the alternative and add a default if it helps. The data then can't go empty just because the application forgot.

When you do allow NULL, say why. If "not entered yet" and "deliberately blank" are different states in your business, NULL is the right tool. One line in the column description keeps the next person from tightening it to NOT NULL.

Remember three-valued logic. Comparisons against NULL are neither true nor false. WHERE status != 'DONE' silently drops rows where status is NULL. COUNT skips NULLs and AVG averages without them. It's the first thing to check when a report looks wrong.

UNIQUE and NULL interact differently across systems: most treat NULLs as distinct, so several rows can hold NULL in a UNIQUE column. If uniqueness genuinely matters, pair it with NOT NULL.

Two column types that keep causing incidents

Timestamps

Put created_at and updated_at on every table. Without them there's no way to reconstruct when a row arrived, and during an incident that absence sets the pace of the whole investigation. Whether defaults and auto-update happen in the database or the application is a team decision; making it once is what matters.

Decide how deletion works. Soft deletion, which writes a time into deleted_at instead of removing the row, helps with recovery and history, at the price of one more condition in every query. Applied to half the tables it produces deleted data reappearing somewhere, so choose per table and record the choice in the specification.

Soft deletion versus UNIQUE constraints

Adopt soft deletion and you'll run into this sooner or later. With UNIQUE on a member's email and account closure implemented as a soft delete, the same person can never sign up again with that address. The row is still there, so UNIQUE rejects the new one.

How you solve it depends on the system:

• PostgreSQL handles it cleanly with a partial index. CREATE UNIQUE INDEX ... ON members (email) WHERE deleted_at IS NULL enforces uniqueness only among live rows.
• MySQL has no partial indexes. The common workaround is including a deletion marker in the unique combination. Since NULLs are treated as distinct, teams usually add a separate column holding a fixed value while the row is live and something unique once it isn't.
• Or change the value on delete. Rewriting the address to [email protected] at closure removes the collision entirely. It also interacts with your data retention policy, so decide it with whoever owns that.

Whichever you choose, list the UNIQUE columns before you introduce soft deletion. Blocked signups are almost always discovered in production.

Money

Amounts need a currency and, when conversion is involved, a rate and the moment it applied. Without them a total can't be reproduced later. Copying the unit price onto an order line at purchase time belongs to the same idea: it isn't duplication, it's recording a fact as of that moment.

Relationships and constraints: what the database enforces

Decide whether foreign keys are enforced. With FK constraints, invalid references fail at the database level and the relationship stays visible in the schema and in any diagram generated from it. Bulk loading and sharded setups sometimes argue against them, but for ordinary business systems the balance favors enforcing. If you decide not to, write that down so the next person doesn't read the absence as "no relationship".

Set delete rules per relationship. RESTRICT as the default, CASCADE only where children are meaningless without the parent. What happens to a member's posts at account closure is a business rule, not a technical one, so decide it with the people who own the rule.

UNIQUE applies to combinations too. Stopping the same member from adding the same product to a cart twice needs UNIQUE on (member_id, product_id). Checking in application code before insert loses that race under concurrency.

CHECK constraints guard value ranges. A rating between 1 and 5 is a good candidate: CHECK (rating BETWEEN 1 AND 5).

There's a version trap here. MySQL only started enforcing CHECK constraints in 8.0.16. Earlier versions parsed the syntax and silently ignored it. If your schema came from an older server, constraints that look enforced may never have been, so verify the existing rows satisfy them before upgrading — otherwise turning enforcement on is where you find out. NOT ENFORCED exists for the case where you want the constraint defined but not applied yet, which is useful mid-migration.

Indexes: decide only what you already know

Indexes trade write speed and storage for read speed. At design time, commit to the certain ones and leave the rest for evidence.

Index foreign key columns. Looking up children by parent always happens. MySQL's InnoDB creates these automatically; not every system does, so verify rather than assume.

Column order makes a composite index. An index on (member_id, created_at) serves lookups by member and newest-first ordering within a member, but does nothing for a query filtering on date alone. Equality columns first, range and sort columns after.

Record why each index exists. A list of names and columns gives the next maintainer no way to judge whether one is safe to drop. "For a member's recent posts list" in the specification keeps useful indexes alive.

Designing for what comes later

Keep code values in tables. Order status and membership tier grow over time. A code table holds a label, a sort order and an active flag alongside the code, which the UI needs anyway.

Decide up front whether history matters. If anyone will need the previous value of a column, that needs a history table or a snapshot column now. Added later, the intervening data is simply gone.

Add languages as rows, not columns. name_ko, name_en turns every new language into a schema change. A translation table keyed by language code turns it into data entry.

What changes on PostgreSQL

Most of the criteria above carry over, but a few decisions go differently.

Length on text types matters less. In PostgreSQL there's no performance difference between varchar(n) and text. A length limit expresses a business rule rather than a storage decision, so many teams cap only what genuinely needs capping and use text elsewhere.

Prefer IDENTITY over serial. serial is the legacy spelling; the standard GENERATED ALWAYS AS IDENTITY is now recommended and handles sequence ownership and permissions more cleanly.

Default to timestamptz. Despite the name it doesn't store a zone: it normalizes to UTC on write and converts to the session zone on read. That takes most time zone handling out of application code, which makes it a sensible default for anything multi-region.

Partial indexes exist. The soft-deletion collision above collapses into WHERE deleted_at IS NULL, and the index stays smaller because it only holds matching rows. MySQL has no equivalent, so a design that spans both systems has to know this difference.

Case handling is close to the opposite. PostgreSQL is case-sensitive by default and folds unquoted identifiers to lowercase. When a schema migrated from MySQL shows unexpected names, this rule is usually why.

If the same design has to ship for two systems, start by writing the type mapping down. The syntax differences between DBMS families are covered from a parser's point of view in SQL to ERD.

The review checklist

With the design finished, walk the diagram against these.

• Singular/plural convention consistent across tables
• No names colliding with reserved words
• Column naming consistent for foreign keys, timestamps and booleans
• Every table has a primary key
• Surrogate versus natural key chosen for a stated reason
• Integer width sufficient for fast-growing tables
• Money columns are DECIMAL
• Time zone basis for timestamp columns is decided
• created_at and updated_at present
• Deletion approach (hard or soft) decided per table
• Nullable columns carry a reason in their description
• The same concept uses the same type across tables
• Foreign keys sit on the N side
• Delete rules set per relationship
• UNIQUE on the combinations that must not repeat
• Foreign key columns indexed
• No stored values that could be computed
• No column holding several values in one cell
• Code values not scattered across tables
• Every column has a one-line description

If choosing entities and keys in order is still new, How to Draw an ERD works through it with a forum example, and how far to split tables is covered in Database Normalization.

FAQ

Is a surrogate primary key always the right answer?

For most business tables, yes. Values that carry business meaning (an email address, a tax ID) can change or be reused, and when they do, every table referencing them has to change too. The exceptions are stable master data like country or currency codes, and junction tables where the composite of two foreign keys is simpler than an extra column.

What type should money be stored in?

DECIMAL. FLOAT and DOUBLE are binary floating point and can't represent values like 0.1 exactly, so summing thousands of rows drifts by fractions of a cent. Most reconciliation bugs start there. If you handle more than one currency, keep a currency code column next to the amount.

Should every column be NOT NULL?

No. NULL is a legitimate way to record that a value doesn't exist yet. The problem is mixing NULL, empty string and zero for the same idea. If you need to tell 'not entered yet' apart from 'deliberately blank', NULL is right; if that distinction doesn't matter, NOT NULL with a default keeps queries simpler.

How many indexes should be decided at design time?

Foreign key columns and the filters you already know about. Leave the rest until real queries and real data exist, then read execution plans. Indexes speed up reads at the cost of writes and storage, so ones added on a guess usually deliver the cost without the benefit.

Agree on naming and primary-key rules first, choose types from the actual data, and record NULL and deletion policies. That keeps the same design questions from being reopened in each table review.

Once the design is settled and you have DDL, pasting it into WorksCove ERD produces a diagram and a table specification from the same schema. The paste-to-result path is covered with screenshots in SQL to ERD.