Designing Databases

Designing Databases

The process of creating, implementing and maintaining the enterprise data in a system is known as Designing of databases. Better understanding of the application is necessary before designing the database. The performance and success of an application depends on good database design. MySQL provides performance dashboard, reports and statistics regarding the design of database.

Create Conceptual Design

It is the primary phase in database design, where detailed discussion about the creation of databases, tables, columns and data types is discussed based on the requirement of the application. As an end result the model is framed to attain the expectation of the application’s end user.

• Create Database

The three major parts that forms a database are Tables, Queries and Views.

Tables - similar to an excel sheet, containing multiple rows and columns. Where each row is a record and each column is an attribute.

Queries – It is a question with multiple conditions posted to the database. The records in the database that satisfies the passed conditions are retrieved.

Views – A set of stored queries.

Example: create a database to store the personaldetails.

mysql> create database personaldetails;

Query Ok, 1 row affected

mysql> USE personaldetails;

Database changed.

The created database is listed using SHOW command. See Table 3.12

mysql> show databases;

Database List

Database: employee, personaldetails, sports

• Create Table

In an application, each page reveals some functionality. Each such functions are designed to a table. For example, in an online shopping site like Amazon, multiple pages are maintained like customer profile, products, orders in cart and payment page. All these can be created as tables like Customer, Products, Order and Payment respectively.

• Create Columns

Each table will have many columns related to the functionality of the table. This column determines what values are stored in the table. For example, the Customer table contains the columns like firstname, lastname, phone, email, age, address and pincode. These columns hold the Customer information in the table. Each column is assigned with appropriate value type. The efficiency and performance of the table purely depends on the data types assigned to the columns.

• Insert Rows

Once the database is created, tables and the columns with the appropriate value type are defined. Then records are inserted to the table.

INSERT INTO TABLE_NAME (column1, column2, column3, ... columnN)

VALUES (value1, value2, value3, ...valueN);