This content originally appeared on DEV Community and was authored by Roxana Maria Haidiner
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 the 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:
- Right-click in the diagram area
- Select Create Table
- Type the table name – for example,
courses - Add columns:
-
course_id– Primary Key course_namestart_dateend_date
-
- 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:
-
Drag from the
student_idcolumn in theenrollmentstable -
Drop onto the
student_idcolumn in thestudentstable - 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
- Give the foreign key a name (e.g.,
- 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 the 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:
- Complete your schema design
- Go online and connect to PostgreSQL
- Open the Synchronization dialog
- Review the differences between your model and the database
- 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 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, which is interactive and low in 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.
This content originally appeared on DEV Community and was authored by Roxana Maria Haidiner
Roxana Maria Haidiner | Sciencx (2025-08-20T11:14:15+00:00) How to Design a PostgreSQL Schema Visually (Step-by-Step). Retrieved from https://www.scien.cx/2025/08/20/how-to-design-a-postgresql-schema-visually-step-by-step/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.