How to Design a PostgreSQL Schema Visually (Step-by-Step)

1. What is a Schema?

In PostgreSQL, a schema is just a folder inside your database where you keep your tables, views, and other objects. You can even have the same table name in different schemas. It’s best to name it after your app so it’s easy to keep things organized.

In our case, we’ll create a schema called school.

Example in SQL:

CREATE SCHEMA school;

In DbSchema tool, you can create it following these steps:

• 1. Start a new schema from scratch in the "Welcome Screen".
• 2. Select the PostgreSQL database and name the project (model) as you wish.
• 3. Right-click in the database tree → Create → Schema → give it a name, like school, in our case.

2. Why Visual Design Helps?

When you design visually a ER diagram:

• You see all tables and how they connect
• Which columns are keys or have constraints
• You can drag to create foreign keys (relationships)
• The tool writes the SQL for you

3. Example Database

For our example, let’s design a database for a school system. We’ll start with three main tables:

• students - information about students
• courses - information about courses
• enrollments - which student is in which course

When adding columns, choose PostgreSQL-specific data types:

• SERIAL - auto-incrementing integers, often used for primary keys
• VARCHAR(n) - variable-length text
• DATE - calendar dates
• BOOLEAN - true/false values

1. Creating a Table with SQL

Usually, when working directly in PostgreSQL, you would create a table by writing SQL in the SQL editor. For example, here’s how we can create a students table inside the school schema:

Example: students table

Why school.students? You write the schema name first (school) because you can have multiple schemas in the same database, and this keeps your tables organized.

After running this SQL, DbSchema will show the table visually in the diagram, with all columns, keys, and data types.

2. Creating the Second Table Visually

For the next table, let’s use DbSchema’s visual design instead of writing SQL:

1. Right-click in the diagram area
2. Select Create Table
3. Type the table name – for example, courses
4. Add columns:
   • course_id – Primary Key
   • course_name
   • start_date
   • end_date
5. Mark the primary key column

The best part: While you work visually, DbSchema shows you the SQL code it generates in real time. You can copy or run that code directly if needed.

4. Creating a Foreign Key in DbSchema

1. Creating a Foreign Key Using SQL

You can create a foreign key directly in the SQL Editor. For example, linking enrollments.student_id to students.student_id:

Example for a cascade rule:

Once you run this command, DbSchema will update the diagram so you can see the foreign key visually.

2. Creating a Foreign Key Visually

You can also create foreign keys without writing SQL, using the diagram:

1. Drag from the student_id column in the enrollments table
2. Drop onto the student_id column in the students table
3. In the Edit Foreign Key dialog:
   • Give the foreign key a name (e.g., fk_enrollments_students)
   • Verify the referring and referred columns
   • Optionally set On Delete or On Update actions
4. Click OK

The foreign key line will appear in the diagram, and DbSchema will generate the SQL automatically in the background.

4. Add Comments and Tags

While designing your schema in DbSchema, you can add:

• Comments to tables or columns to explain their purpose
• Tags to group related tables or mark certain objects for review

These notes are saved in your model and can be included in generated documentation. This is useful when working in teams, so everyone understands the meaning of each table and column.

5. Synchronize Schema Changes

If you are connected to your PostgreSQL server from the start, DbSchema will apply changes immediately as you create or edit tables.

If you design offline:

1. Complete your schema design
2. Go online and connect to PostgreSQL
3. Open the Synchronization dialog
4. Review the differences between your model and the database
5. Choose which changes to apply to PostgreSQL

This way, you can work without affecting the live database until you’re ready.

6. Save and Share Your Schema

Once your schema is ready, you can save it as a model file (.dbs). This file is actually XML and contains your entire project, including tables, relationships, comments, and tags.

Working in a Team

You can store this .dbs file in a Git repository so multiple team members can work on the same schema. DbSchema has Git Integration built in, making it easy to pull updates, merge changes, and keep everyone in sync.

Sharing the Design

You can share your work easily:

• Export as interactive HTML5 documentation
  • Includes your ER diagram and all schema details
  • Displays table structures, relationships, and comments
• No DbSchema required for viewers
  • SVG image wich is interactive and low size
  • Team members or stakeholders can open it directly in their browser
  • They can explore the diagram and table details interactively

Conclusion

A PostgreSQL schema is a way to organize your tables and other database objects so everything stays clear and structured. You can create it with SQL commands or design it visually for a more intuitive experience.

If you want to try designing a PostgreSQL schema visually for free, download DbSchema.