One of the most important parts of developing a project is having a clear picture in mind of the end goal. We need to know the target audience for the project, as well as the features it will include. This means that we need to be as informed as possible about the business logic, and then implement all the features as needed.
DBDesigner is a great tool when it comes to creating database schemas for your application. It allows you to create any number of tables you want (as far as I am concerned). You can add any data type attribute you want to any table you have created. You can also have certain attributes serve as foreign keys. This way, when you set up primary keys and foreign keys respectively, you can see relations between tables of the database that you are trying to create.
You can use your email and create many projects, and get back to them whenever you want. You can also invite your colleagues via email and have them collaborate with you in the preparation of that schema.
When you have an initial version of your database schema, you can then export it as an SQL script for the following database technologies: PostgreSQL, SQLite, MySQL, MSSql, and Oracle.
Demonstration
Let’s start by creating a new database schema to demonstrate how it works in practice.
We can either start with a new blank template, or use one from the many pre-existing templates that are available.
We will be demonstrating an empty template here, just so that we can see some of the features that are included. Otherwise, you may not notice them using existing templates.
First we need to create a new schema. Our example uses the “Generic” database type, and we will call it “library”.
So, we need to go to Schema > New and then we will see a new window will pop up:
This is the image that we should see after that:
Then we need to add new tables into our schema, which we can do by right clicking anywhere on the grid, and selecting the “Table” option:
Now we need to add fields to the table. All we have to do is go to Add field, after which a new window will show up. In it you can choose the type, and also set up a few constraints to your new table column:
Here we can see how it looks like after we have added a few columns:
Then we can add relations between tables. We will take the example of creating a many-to-many relation between two tables: Authors and Books. For this, we initially need to create a new table called AuthorBooks, in which we add foreign keys that reference the Authors table and the Books table, respectively:
Here we have the connection with the Books table:
After we are done with that, we should see a schema similar to the following:
A really great feature of dbdesigner is the flexibility it gives you to move your tables around the grid as you wish:
We can also share the schema with up to five collaborators in the free version. We simply need to go to Schema > Share and a new window will pop up, like the following:
We can save this schema as an image by going to: Export > Image.
We can also generate the corresponding SQL script the following way:
We can also import our own SQL into the schema and see it represented graphically:
Conclusion
I heard about this tool when I was doing a pair-programming with a colleague, and have found it really helpful. I hope you benefit from it as well.
DBDesigner has other features, and I would definitely recommend that you give them a try.