MySQL - Create relational database with schema

As mentioned in previous articles, MySQL is the programming language to manage and control the data in relational database. Nowadays, there is graphical query builder or popular web framework like Django so that you no longer need to code with SQL statement. Nonetheless, I still recommend to understand the principle of relational database and SQL especially who have less knowledge of database system design. There are several relational databases including MySQL, SQLite, PostgreSQL. In this tutorial, we will take an advantage of MySQL to administrate our own database.

Imagine that you are the shop owner and start up a business, however,  resources are lacked to hire a technical to help you to construct the database structure. What could you do? Ask help? No, just do it by yourself~! Before building the operational database, we better to know the data flow in the process under this scenario.

If you have never heard relational database model, you are recommended to understand more about databases design.

In daily basis, numerous data are imperceptibly generated and stored in daily operation. As a ship owner, you are selling the products with the details of specification and price, which are produced and supplied from vendors.Therefore, the contact and address of the vendors is needed for communication and logistics arrangement. Of course, you are responsible to serve the clients. Some of them have joined as a member after submitting the application form which consists of their personal information. While the member purchases the product, transaction will be taken placed and the records of payment details, consuming amounts.

The bold objects represent entities and Italics implies the relationship between the entities.
The above statement is presented by ERD:

Additionally, the underline field is the attribute of the entities which are the details and information. For the primary keys, it occurs in each table. Taking product table as an example, a foreign key, vendor_id is assigned to product table. The vendor's information are repeatly stored in product table so that data redundancy can be eliminated. After adding the keys and attributes on each entities, the tables in a relational database are constructed as follows:

MySQL
Then, you could run the MySQL in the terminals / CMD depends on the OS in your computer. If you have forgotten or skipped the last tutorial, please refer to the last tutorial to install and execute the MySQL shell. According to ERD shown above, 4 tables will be created in one database

1. Create a Database named "Shop"

CREATE DATABASE Shop;
USE Shop;

2. Create Tables for each Entity
(i) Member

CREATE TABLE member(
member_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
member_name varchar(25) NOT NULL,
member_contact varchar(25),
member_address varchar(25),
membership_type varchar(25),
join_datetime varchar(25),
birthday varchar(25)
);

(ii) Product

CREATE TABLE product(
product_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
product_category varchar(255),
price decimal(10,2) NOT NULL,
discount decimal(2,2),
vendor_id int NOT NULL 
);

After created the Vendor tables and assign "vendor_id" to the foreign key:

ALTER TABLE product
ADD FOREIGN KEY(vendor_id)
REFERENCES vendor(vendor_id);

(iii) Vendor

CREATE TABLE vendor(
vendor_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
vendor_name varchar(25) NOT NULL,
vendor_contact varchar(25),
vendor_address varchar(25)
);

(iv) Transaction

CREATE TABLE transaction(
transaction_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
member_id int NOT NULL,
product_id int NOT NULL,
payment_datetime varchar(25),
payment_terms varchar(100),
payment_method varchar(25),
quantity int NOT NULL,
total_amount decimal(10,2) NOT NULL
);

Assign both member_id and product_id as foreign key:

ALTER TABLE transaction 
ADD FOREIGN KEY(member_id)
REFERENCES member(member_id);
ALTER TABLE transaction 
ADD FOREIGN KEY(product_id)
REFERENCES product(product_id);

3. Check the details of tables

SHOW TABLES;
DESCRIBE member;
DESCRIBE product;
DESCRIBE vendor;
DESCRIBE transaction; 

Good Job! We have established the database of your own shop with schema. It is available to insert and store the information into the tables. As mentioned preciously, graphical query builder is open to download for constructing the tables with attributes in database. MySQL workbench is one of the tools to build the ERD and database without any SQL syntax.

Download the MySQL workbench:
http://dev.mysql.com/downloads/workbench/5.1.html