Database Schema Designer

Design tables, columns, keys and foreign key relationships in a form and export MySQL-style CREATE TABLE and ALTER TABLE SQL. Saved in your browser.

The interactive tool loads here when JavaScript runs.

About this tool

What is the Database Schema Designer?

The Database Schema Designer lets you sketch a relational schema without writing DDL by hand. You create tables, add columns with a type, length, nullability, primary key flag and default, then link tables with foreign key relationships. Generate SQL writes a CREATE TABLE for every table and an ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY for every relationship.

The type list (INT, BIGINT, VARCHAR, TEXT, MEDIUMTEXT, DATETIME, TIMESTAMP, BOOLEAN, JSON, BLOB and more) follows MySQL, so the output runs on MySQL and MariaDB as written. Most of it also runs on PostgreSQL; types like DATETIME, MEDIUMTEXT and BLOB need converting, which the SQL Data Type Converter can do.

Your design is stored in this browser's localStorage, so it survives a page reload, and it is also mirrored into the page URL's query string, so copying the address bar shares the design. There is no account or server-side storage. Copy or download the SQL to keep a durable copy.

  • Tables, columns, lengths, nullability, primary keys and defaults
  • Composite primary keys written as one PRIMARY KEY clause
  • Foreign key relationships as named ALTER TABLE constraints
  • Correct quoting of default values
  • Design saved in localStorage and the page URL
  • Copy or download as database_schema.sql
Read the guide: Database Schema Design and ER Diagrams: Keys, Relationships and Normal Forms

Updated

How to use the Database Schema Designer

  1. Click New Table (or Load Sample to start from a users and posts schema). The new table opens in the editor with an id primary key.
  2. Set the Table Name, then use Add Column for each field: name, Data Type, Length for VARCHAR, CHAR or DECIMAL (for example 10,2), the Nullable and Primary Key checkboxes, and an optional Default Value. Click Save Table.
  3. Under Table Relationships, click Add Relationship and choose From Table and From Column (the foreign key) and To Table and To Column (usually the parent's primary key).
  4. Click Generate SQL, then copy it or download database_schema.sql.

Worked example

A table with a timestamp default and a composite key

Input

Table post_tags with post_id INT and tag_id INT both ticked as Primary Key, plus created_at TIMESTAMP not nullable with default CURRENT_TIMESTAMP

Output

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (post_id, tag_id)
);

Two key columns become one table-level PRIMARY KEY (post_id, tag_id) rather than two inline PRIMARY KEY clauses, which every database would reject. CURRENT_TIMESTAMP is left unquoted so it is a function, not the literal text.

Background and insight

Relational schemas go back to Edgar Codd's 1970 paper on the relational model, and the normal forms he and others defined in the early 1970s are still the working checklist: one value per column (1NF), every non-key column depends on the whole key (2NF), and nothing depends on another non-key column (3NF). In practice that means one table per real-world thing, a primary key on every table, and foreign keys instead of copied data.

A foreign key is declared on the child side. posts.user_id REFERENCES users(id) says each post belongs to one user and one user can have many posts. That is the only kind of relationship SQL can enforce directly. A one-to-one is a foreign key that is also unique. A many-to-many needs a third, junction table (post_tags with post_id and tag_id) holding a foreign key to each side; when you mark a relationship many-to-many here, the SQL includes a comment reminding you of that, because a single foreign key cannot express it.

Defaults are a common source of broken DDL. DEFAULT CURRENT_TIMESTAMP must not be quoted, while a text default must be. The designer leaves numbers, NULL, TRUE, FALSE, CURRENT_TIMESTAMP-style keywords and function calls like now() unquoted, and quotes everything else with single quotes doubled.

Common use cases

  • Planning a new feature

    Lay out the tables and keys for a feature before writing the migration, and paste the SQL into the pull request for review.

  • Teaching relational design

    Show how primary keys, foreign keys and junction tables fit together with a schema students can change and regenerate.

  • Prototyping a side project

    Get a working MySQL or MariaDB schema in a few minutes, then refine it as the app grows.

  • Feeding other tools

    Paste the generated SQL into the ERD Generator for a diagram, or into the Seed Generator for test rows.

Tips and limitations

  • The output does not add AUTO_INCREMENT. Append it to the id column (MySQL) or switch to GENERATED BY DEFAULT AS IDENTITY on PostgreSQL.
  • Name foreign key columns after the table they point to (user_id, order_id). Each constraint is named fk_<table>_<column>, so two foreign keys to the same table get distinct names.
  • Index your foreign key columns. MySQL InnoDB creates one automatically; PostgreSQL does not.
  • Renaming a table updates relationships that use it, and deleting a table removes them. Regenerate the SQL afterwards.
  • Clearing site data in your browser deletes the saved design. Download the SQL if it matters.

Sources and further reading

  1. A relational model of data for large shared data banks, Communications of the ACM. Codd's 1970 paper that introduced the relational model
  2. Constraints, PostgreSQL Global Development Group. Primary keys, foreign keys, UNIQUE and defaults
  3. CREATE TABLE, PostgreSQL Global Development Group. DEFAULT expressions and column definitions

Frequently asked questions

Which database does the generated schema SQL target?

MySQL and MariaDB. The type list and syntax follow MySQL, so the script runs there unchanged. PostgreSQL accepts most of it, but types such as DATETIME, MEDIUMTEXT, TINYINT and BLOB need mapping first; paste the output into the SQL Data Type Converter to do that.

How do I model a many-to-many relationship in SQL?

Create a junction table with a foreign key to each side, for example post_tags(post_id, tag_id), and make the pair its primary key. A single foreign key can only express one-to-many or one-to-one, so the designer adds a comment to many-to-many relationships instead of pretending one constraint is enough.

Why did my DEFAULT CURRENT_TIMESTAMP fail before?

Because quoting it as 'CURRENT_TIMESTAMP' makes it a string, and a string is not a valid timestamp. The designer leaves CURRENT_TIMESTAMP, CURRENT_DATE, numbers, NULL, TRUE, FALSE and function calls unquoted and quotes everything else.

Can a table have more than one primary key column?

Yes, as a composite key. Tick Primary Key on each column and the designer writes a single PRIMARY KEY (a, b) clause at the end of the table. A table can only have one primary key, but that key can span several columns.

Where does the schema designer save my tables?

In your browser's localStorage under the key database-schema-designer, so reloading keeps your work on this device. There is no account or server-side copy, so download the SQL if the design matters, and avoid sharing links that contain sensitive table names.

Does the designer create indexes or auto-increment columns?

No. It writes columns, primary keys, defaults and foreign keys. Add AUTO_INCREMENT, identity columns, UNIQUE constraints and secondary indexes to the generated SQL yourself, or use the SQL Index Optimizer to decide which indexes your queries need.

Is there an in-depth guide to the Database Schema Designer?

Yes. “Database Schema Design and ER Diagrams: Keys, Relationships and Normal Forms” covers the background, how it works, common pitfalls and worked examples. Read the full guide.