dijexi.com

In the previous parts of this tutorial series we have discussed how to setup the application environment using XAMPP and CodeIgniter, and the application specification. We have done the analysis and design using UML use case and class diagram with free UML diagram tool StarUML.

In this part, we will discuss the database design for our application. This database is needed to hold the data for our application.

We are going to draw the entity relationship diagram (ERD) using a free tool called MySQL workbench, formerly fabForce ERD tools for MySQL. Before continuing, please download the program here and install it to your computer.

At the end of the designing process, we will create our database structure directly to a MySQL server that can be used by our application.

The Tables

For the purpose of our application requirements and based on UML use case we defined before, here is a list of mandatory tables that we must create on the application’s database.

Creating Tables in MySQL Workbench

Installing MySQL Workbench should not be a problem. I assumed that you already have done that. Now, run the program and you will see a blank workspace of MySQL model like this:

Click on Add Diagram icon to create our first diagram. You will see a blank page for drawing our diagram like this:

Creating A New Table and Adding Fields

Next, we need to draw a table. Click on the “Place a New Table” located on the left side of the blank diagram. Then place it on the middle of the blank page. After placing the table, you may resize it’s size so that we can see a tall rectangular like this:

Double click on the table to add it’s columns. You will see a new pane at the bottom of the page consisting the table properties like this:

On the pane, there’s a lot of tab on it’s bottom part. Click the “Table” where we can give our new table a name. Create our first table called “cashbook”.

Next we need to define the table columns. Click on the “Columns” tab. You will see the following:

As you can see, the program automatically add a column called “idcashbook” which you can rename it. This column should be the table’s primary key, so we check on the NN (means not null), AI (auto increment), and Flags PRIMARY KEY. When we set it up, then the icon left to the column name will change to a yellow key icon.

Next, we need to add more column. To do that, double click the empty row below the first column. Enter “trxdate” as the column name and DATETIME as the type. Your table property should be like the following:

Repeat the step of adding new field for the following new fields:

Creating Other Tables

Here is a list of tables with it’s description that we need to create for our application. Repeat the above steps to create every table listed below.

Table cashbook (should have been created before)

This is the master table of cashbook document. It has it’s detail records in cashbook_details table.

Table cashbook_detail

This is the detail records of table cashbook.

Table bankbook

This is the master table of bankbook document. It has it’s detail records in bankbook_details table.

Table bankbook_detail

This is the detail records of table bankbook.

Table coa

This table store the chart of account data. It will be referenced by cashbook_detail, bankbook_detail, and journal table.

Table coatype

This table store the COA type

Table journal

This table store journal transactions generated by cashbook and bankbook transaction after being posted and manual journal transaction entered manually.

Creating Relationships

To create a relationship between two table in MySQL Workbench, simply click on the “Place a Relationship Using Existing Column” icon on the left side of the diagram. Then:

1. set the FOREIGN KEY column on the referencing table, and
2. set the PRIMARY KEY column on the referenced table

For example, to create a relationship between cashbook (the referenced table) and cashbook_detail (the referencing table):

1. Click “Place a Relationship Using Existing Column” icon

2. click cashbook_idcashbook column on the cashbook_detail table and click “Pick Referenced Column” button

3. click idcashbook column on the cashbook table

A relationship line will be drawn between those two tables.

The Finished ER Diagram

After you have created all the tables and relations as specified above, here is the completed ER diagram will looks like:

And here is the diagram MWB file for you to download.

Creating the Database into MySQL Server

After finishing the design of the ERD we need to create the database structure directly into MySQL server. To do this first we need to define the database server connection. Click on the menu Database – Manage Connections… You will see a dialog like this:

Click on New button and enter the Connection Name for example localhost, the Database System of MySQL, and the Driver of MySQL Native Driver. On the Parameters pane, enter your hostname or IP address on the Hostname field, Port number which is default to 3306, the Username and Password which match to your MySQL server configuration.

You may optionally click on Test Connection to make sure that the connection to the database server was successful. Click Close button.

To generate the database structure first we need to define the database name to be created. Click on the MySQL Model tab on the main page of MySQL Workbench. Then double click on mydb tab under Physical Schemata section. Look under the mydb property pane. Change the Name to the database name to be created, for example acctdb.

Don’t forget to click on Save Current Model icon to save our diagram.

Next, click on the menu Database – Forward Engineer.. to begin creating the database structure. A dialog box will appear:

Click Run Validations to make sure that the table definitions and relationship in our diagram was all correct. Then click on Next button. The next dialog box will appear:

On that dialog, you specify the options for creating the database. The first options is to drop objects before each CREATE Object, and the second is to generate separate CREATE INDEX statements. Then click on Next button. The next dialog box will appear:

This time we select which objects in our diagram that will be generated on the MySQL database. Select all tables. Then click on Next button. The next dialog box will appear:

Here we can review the SQL statements to be executed to the MySQL Server. As you can see, the SQL statement was generated based on the diagram that was created before in the design. This including the database, tables, fields, index, and the relations between the tables. Then click on Next button. The next dialog box will appear:

Here we must choose the database connection that we have defined before. If there’s a change on the hostname, username and password, you still can do it here by modifying it on the appropriate field. Then click on Execute button to execute the SQL statement on the server. The next dialog box will appear:

The execution is now complete. It means that the database is created on the server with the tables and relations as defined in our diagram. You may check that on any MySQL front end program like phpMyAdmin, Navicat MySQL, and any other programs. Here is the snapshot of the database structure as viewed with Navicat MySQL program:

In this part, we have discussed the database design for our application. We have drawn the entity relationship diagram (ERD) using a free tool called MySQL workbench. Also we have created our database structure directly to a MySQL server that can be used by our application. You may download the diagram MWB file here.

In the next article, we are ready to begin the coding of the application.