http://www.developer.com/db/choosing-a-mysql-case-tool.html

Back to article

MySQL Database Creation with CASE Tools


December 12, 2011

CASE (computer-aided software engineering) can be very helpful when creating new software or a Web application, with many features that can increase productivity and speed up the creation process. There are many CASE tools available, with some specialized for certain parts of software development. For example, CASE tools can be used for database programming. Because database modeling is an integral part of every program and MySQL is a very popular database server, in this article I will explain how to use CASE software to create a MySQL database.

Why Use CASE Software to Create a MySQL DB?

If you are accustomed to creating your SQL code manually and visualizing your MySQL database model in your head, you may ask yourself what the advantages of using CASE tools are. Well, MySQL CASE tools have many benefits:

  1. You don't have to write code--this is done automatically. By clicking export when the modeling is finished, you will get your SQL create script.
  2. The database diagrams created are much easier to work with than code; they help you see the whole database and its table relations much better.
  3. Database relations and data types are pre-made, which means minimal repetitive tasks and wasted time.
  4. Once created, a MySQL database diagram can be saved as an image or XML file, which means that you can easily send it to your colleagues.
  5. You can connect to the database and synchronize it.

In short, use MySQL CASE software to become a better and faster coder.

MySQL Database Diagrams

Database creation is not a one-step task. In order to create a good database, you have to make an abstract model of it before generating your SQL code. This abstract model is known as an EER (extended entity relationship) diagram. Let's review its main characteristics.

While creating the diagram, you have to think of your database as a whole and identify the objects in your application as well as the relationships between them. There are a few relationship types:

  • One-to-one: exactly 1 object corresponds to exactly 1 other object
  • One-to-many: 1 object is related to many other objects
  • Many-to-many: Both objects can be related to many objects of different types

For example, if you are creating a website about car prices, car, make and dealer would be some of the objects. A car can have only one manufacturer, but the manufacturer can make many car models (one-to-many relation); while a dealer can sell many different cars and one car can be found in many dealer shops (many-to-many). Here is an example of an EER diagram.

There are two more types of relations: generalization and specialization. They are very important for reducing redundancy and creating class hierarchy later. Generalization is the process of grouping two similar objects into one more general object. Specialization is the opposite process. Let's take ad networks for example. They have two types of users: publishers and advertisers. Since both advertisers and publishers are actually users, they have some identical attributes, which are grouped (generalized) as users.

Which MySQL CASE Tool to Use

As I mentioned previously, there are many CASE tools on the market. Among those that aren't free, I would point out MicroOLAP. Its price is not high ($125) compared with some other CASE software solutions, which can cost more than $1000. Also, MicroOLAP allows you to create stored MySQL procedures, a feature that is not supported by most other database modeling tools.

For free CASE tools, the best known products are MySQL Workbench and DBDesigner. Their features are pretty similar. However, I prefer DBDesigner slightly because it doesn't require the installation of Visual C++ Studio and it is a bit simpler to use.

When you have installed and opened DBDesigner, you will get a new blank sheet. On the right side you will see the data types, which can be grouped by type and edited. You can also add your own data types that you use frequently in your applications (e.g. it is good to add int(10) as a new datatype, as it is the most common primary key type in MySQL). Below the data types you will see the table list. You can sort the tables alphabetically or by region (useful for large database structures).

On the left side, you can see the database table relations. Just click on the desired relation, select the first table and then the second, and DBDesigner will create the appropriate foreign keys or new tables if necessary. By going to file tab and selecting export, you can generate the MySQL create code, or save the database diagram as an image or an XML file.

You can also use reverse engineering: create the EER diagram from a database that is already built. Go to the database tab and click reverse engineering, enter your database connection info and you will get an EER model created automatically. After making changes, there is a feature to synchronize the database and insert new changes.

The Risks of MySQL CASE Tools

Although I have pointed out many advantages of MySQL CASE tools, there are also some risks associated with them. However, these risks affect mostly business users. My advice is to use CASE tools from only one company, and make sure the first project you use them on does not have a tight deadline, as they require organizational changes and additional training for your programmers and database experts.

Apart from these caveats, I recommend everyone to start using MySQL CASE software to increase their speed and productivity when creating MySQL databases.

Sitemap | Contact Us