Of all the objects in a database, to my mind, the table is the most popular. Tables can be created manually and automatically. Manually creating a table is by creating table structure through filling of the required entries in the table design interface of the database. The automatic creation can take any of the following forms-

1. Use of command line to enter SQL statement like MySQL command line.
2. Running SQL table creation statement in the database.
3. Use of scripts to run SQL statements at run-time, that is, when the application is running. Table is created dynamically this way.

All the automatic means involve the use of Structured Query Language (SQL). Structured Query Language (SQL) is a database language that allows a user to –

1. Create the database and relational structure.
2. Perform basic data management tasks, such as the insertion, modification, and deletion of data from relations.
3. Perform both simple and complex queries to transform the raw data into information.

Structured Query Language (SQL) has two components-

1. A Data Definition Language (DDL) for defining the database.
2. A Data Manipulation Language (DML) for retrieving and updating.

The Data Definition Language (DDL) allows database objects such as schemas, domains, tables, views, and indexes to be created and destroyed. Examples of Data Definition Language (DDL) are CREATE TABLE, CREATE VIEW, CREATE DATABASE, ALTER TABLE, DROP TABLE etc. Data Manipulation Language (DML) statements are SELECT, INSERT, UPDATE, and DELETE. INSERT, UPDATE, and DELETE statements are used for modifying contents of the tables in the database. SELECT statement is used for querying database.

The Data Definition Language (DDL) component of the Structured Query Language (SQL) is responsible for the dynamic creation of tables. Dynamic creation of a table at run-time is very useful when a table is required temporarily for the purpose of storing intermediate records pending the time the final records will be generated and stored in the final destination table. Such intermediate tables are set up on ad-hoc basis and the exact time the table will be created may not be known in advance. Let’s look at this and some other areas where the use of dynamic creation of table can be applied.

1. Batch posting- This is a scenario in an online stock broking application where accounting records of clients are prepared in batches before journal posting and as many authorized persons can create as many batches pending the time of journal posting whose time cannot be pre-determined in advance. It may be after 1 day, 2 days etc depending on when the person finishes the entering of data for a given batch. Each batch is created as and when needed dynamically with the CREATE TABLE statement and the uniqueness of the table name is ensured by assigning a unique name dynamically with the aid of a variable e.g. CREATE TABLE $batch_name (for a PHP script). Each batch is posted to the journal with a unique reference number and immediately the posting is over after form submission, a DROP TABLE statement automatically removes that particular batch table from the database. Without dynamic creation of table, this type of task would have been impossible.
2. Installation of application- The power of dynamic table creation at run-time is also demonstrated in the installation of an Open Source application where the database and all the tables, some of which may also contain data, are all created and set up all at once with the use of CREATE DATABASE and CREATE TABLE statements.
3. Database restoration- With the use of Data Definition Language (DDL) of the Structured Query Language (SQL), an entire database can be recreated with the CREATE TABLE statement. The restoration script containing the CREATE TABLE statements recreates all the tables and uploads the backed up data into the appropriate tables in the database within a twinkle of an eye.

Structured Query Language (SQL) and particularly Data Definition Language (DDL) have generic syntax but there are slight variations from one type of database implementation to another. The way you write a CREATE TABLE statement for MySQL implementation may not be exactly the same as in Microsoft SQL Server implementation. So, you have to find out the specific syntax for your working database.

Creating a table dynamically at run-time increases the degree of automation of the application and a highly automated application is what an average visitor would enjoy. When he visits a site, he is only interested in what he sees on the browser. When he issues a search query, what happens back-end is of no interest to him. It is the search result that he is only interested in. But we as web developers, we look beyond the browser.

We are interested in what goes on as far as the database at run-time. For this reason, to be able to develop highly dynamic sites we need to continue to learn Structured Query Language (SQL) more and more. The foregoing account of creating table dynamically is a practical demonstration of this fact. There are many other instances where you can apply this concept apart from the three areas mentioned above. As you develop an application, when the need arises to apply it, you will definitely know as long as you are aware of the concept.

Copyrights reserved.