Webman-framework

Lightweight, Component-based, and Database-oriented Web Application Framework

About | Overview | Documentation

 

Documentation > Tutorial > 5. Database table design

5. Database table design

 

Below is the proposed field names used to store users' entries of mygb application.

Field NameDescription
idA unique reference ID for each entries.
nicknameUsers' nick name. If left blank it will by default set to "Anonymous".
emailUsers' e-mail address.
entryText entry made by user.
date The date when the entry was made ("yyyy-mm-dd" format)
timeThe time when the entry was made ("hh-mm-ss" format)

To effectively use the framework, application database table should be designed following framework's database design CoC. Below is how framework's database design CoC could be applied in the context of mygb application:

  • Table name should begin with the application name: entry table in mygb application should be named as mygb_entry.
  • The primary-key field should named begin with "id_" sub string followed by table name not including the application name part: mygb_entry table should has its primary-key named as id_entry and not id_mygb_entry or simply id as proposed above.
  • For the primary-key, instead of using database default auto-increment value there is an option to auto generate the key value by using built-it function provided by the framework's database API. Using the id_entry example, the primary-key can be alternatively changed from id int auto_incerement to id_entry_36base binary(n) or id_entry_62base binary(n) to fulfill this option requirement.
  • Date and time field’s data could also be automatically generated for both insert/update operations by naming them as follows: wmf_date_created, wmf_time_created, wmf_date_modified, and wmf_time_modified. For the sake of coding simplicity, the previously proposed date and time field names could be altered following this particular convention.
Based on the above database table design CoC implementations, below is the possible SQL statement that could be used to create the table to store users' entries of mygb application.
create table mygb_entry (
id_entry_62base BINARY(6) not null,
nickname VARCHAR(15),
email VARCHAR(25) default '-',
entry TEXT not null,
wmf_date_created DATE,
wmf_time_created TIME,
PRIMARY KEY (id_entry_62base));
The next steps below will show how to create the proposed database design using MySQL command-line interactive client tool inside the command prompt window.

 

At the command prompt window enter the command: mysql -u webman -pwebman db_webman as below.

 

The previous command is used to activate the MySQL command-line interactive client tool using database account with the login: webman, password: webman, and database name: db_webman. If successful, the MySQL client tool will be activated and waiting for user instructions as follows.

 

Copy and paste the SQL statement to create mygb_entry table into MySQL client tool as below. Press enter to execute the SQL statement.

 

Still at MySQL client tool, enter the command: describe mygb_entry; and then press enter. The MySQL client tool should display the structure of the previously created mygb_entry table as below.