Table Designer for MySQL

Table Designer is designed to build and modify a MySQL table with ease in a visual and intuitive interface. Save your time, increase productivity, reduce errors, and ensure a smooth experience with no need to write complex codes.

There may be situations when working with tables require writing complex codes that may take much time and increase the number of errors in the code. To avoid these cases, you can use a visual object editor - Table Designer offered by dbForge Studio for MySQL. The tool displays a grid where you can easily create columns and manage its attributes, add comments and constraints, define indexes, customize table options and handle partitioning, set triggers, and get an output on the data grid view. With the MySQL Table Editor, designing and updating tables become a flexible and enjoyable process that can:

  • Simplify the table creation and modification in MySQL with no code
  • Automatically set data type for frequently-used column values
  • Prevent users from making errors with the table
  • Display the code according to your changes made to the table
  • Automatically generate a SQL script according to the changes made by user

All actions from one grid

dbForge Studio for MySQL Table Designer provides the full set of options you can use to create new MySQL tables or edit data of the table on the grid. It allows adding new MySQL columns in one click, changing column names, and specifying major properties without opening any extra windows. By selecting the corresponding checkboxes, you can easily set and remove primary keys. To override the default database collation, you can set a different collation for a specific column. Also, you can make some notes or add some information about the table using the Comments field.

Under the grid, all changes you make are immediately reflected in the DDL table of the SQL editor. In case the view with the script is not necessary, you can change the window layout and it will be hidden.

MySQL Table Builder of dbForge Studio for MySQL

Using the Heuristics Feature to Optimize a Table

To simplify column creation, the application provides the heuristics feature. It allows the application to automatically set the data type to the frequently-used column name or its part.

This is very comfortable and saves your time because you don't have to replace default data types each time you write the most commonly used column names.

Previewing the Changes in MySQL Table Designer

If you create a new MySQL table or modify an existing one, all changes you make are reflected as the CREATE statement in the application SQL editor on the fly. You can view a script that contains information about the server and client version, a date when the script was generated, a database the table is connected to, and the SQL statement of the table. By default, the script can be generated to a new SQL document after clicking Script Changes. However, if you want to copy the script to the clipboard from the Table Designer, you need to select the corresponding option from the Script Changes drop-down list.

Preview of the changes in MySQL Table Designer

MySQL Constraints

To ensure the data integrity, you can easily work with MySQL CHECK constraints, primary, and foreign keys on the Constraints tab:

  • Create or remove MySQL CHECK constraints, primary, and foreign keys
  • Define a condition for a CHECK constraint
  • Permit or restrict values which you can apply to the column
  • Restrict a data type for the column to be added to the table
How to add MySQL CHECK Constraints

Indexes

To optimize the query and simplify the table grouping and sorting, it is worth using indexes for the table columns.

In a simple MySQL table editor, you can add indexes to display the relevant rows filtered by a specific column value very quickly. This simplifies and accelerates the data retrieval without the need to look through each row from the whole table.

On the Indexes tab, you can do the following:

  • Add or remove unique and non-unique indexes
  • Make indexes invisible
  • Define an index type: FULLTEXT, BTREE, or SPATIAL
  • Set a parser type of the index
Working with Indexes in MySQL Table Editor

Options

On the Options tab, you can easily design a custom table by setting different types of options to the table:

  • Specify a default character set for the table
  • Indicate an auto-increment value for table rows
  • Set a checksum value to ensure table consistency
  • Select the pack keys and delay key write options
  • Set a row format: compact, redundant, compressed, or dynamic
  • Restrict the number of rows in the table
  • Specify the average row length
  • Indicate whether to enable or recalculate persistent statistics
Setting table options in MySQL Table Editor

Triggers

In the MySQL table editor, you can easily create a trigger at a row level. It will be activated in response to the insert, update, or delete events. You can also set whether the trigger is executed before or after the event.

Triggers can help you ensure better data integrity and consistency because they are automatically activated after the changes were made to the table.

Creating triggers in MySQL Table Editor

Partitioning

If you have large tables, you can optimize the way your data is stored and reduce the response time to scan the data by creating a partitioned table. Partitioning allows you to split the table into smaller parts and access each part separately.

Table Designer of dbForge Studio for MySQL supports partitioning (by range, range columns, list, list columns, hash, and linear hash) and subpartitioning (by hash, linear hash, key, and linear key).

On the Partitioning tab, you can select a partitioning type, write a partitioning expression, and refresh the object. After that, the partitioning expression will be displayed in the script in the SQL editor.

Creating a partitioned table in MySQL Table Editor

MySQL Storage Engines

Choosing the most appropriate MySQL storage engine for a table can be critical to achieving maximum performance.

MySQL Table Editor supports different MySQL storage engines to create or update data from the database. They are listed in the Engine field on the Constraints tab. The default storage engine is InnoDB. However, you can easily select the storage engine that will suit your requirements and your specific situation most.

Selecting a MySQL storage engine in MySQL Table Editor

How to Create a MySQL Table

Learn how to create a new table on a particular example using the Table Designer built into dbForge Studio for MySQL.

Conclusion

dbForge Studio for MySQL provides a robust Table Designer that allows you to create a new table, edit data in the table, add indexes, set triggers, define CHECK constraints, customize table options, and split the table into partitions with ease and with no code. All this helps you optimize data storage, minimize the scanning time of the table, and ensure data consistency. Use this functionality along with many other database design and development tools provided by default in dbForge Studio for MySQL.

dbForge Studio for MySQL

The most intelligent MySQL management tool

Download

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise
Professional
Standard
Express
Flat table editor with heuristics support
Yes
Yes
Yes
Yes