How to design the database schema of a system!

  If you have to build a house ,one fine day you don't call a maison and give him the bricks and the other raw materials to start his construction.You start creating a plan for the house well before you actually start implementing it.Once you find there is some feature in your plan that doesn't give you satisfaction,you again change the plan as per your idea.When you make one change you should take care that it doesn't affect your other areas.The same procedure applies to your database design also.You first have to create a design,check for its problems,refine it and again reconstruct it.This process is done till it becomes perfect.

To start with you have to create a database.
It is created by the command.
   CREATE DATABASE DB_NAME;
Once you have created you can view the available databases using the
SHOW DATABASES;

To enter into a Databse give the command,
 use db_name; 

The main entry in a database design is the table.All the attribute that comes along with a table are also specified.When we assign the attributes you should take care that the table doesn't become polymorphic.That is the table should contain the attributes which totally belongs only to the specific table.

The command to create the table is
create table table_name(
e_id int(2),
e_name  varchar(20),
e_desgn varchar(20),
e_add varchar(30)
);

varchar has a maximum of 255 fields ,in case your respective field has only 4 digits in char,give it as varchar(4)..
There are many other datatypes associated with creating a table.They are
Date,Int,Longint,String,Float,BLOB,Tiny,Char(n),TinyInt,BigInt..

Once the tables are created along with the attributes,you can set the conditions to the table that you want the table wants to follow.The constraint can be

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FORIEGN KEY
• CHECK
• DEFAULT

These constraints can either be given while creating the table nor be given after the creation of table..

During Creation......

create table table_name(

e_id int(2),

e_name  varchar(20),

e_desgn varchar(20),

e_add varchar(30),

primary key(e_id),

);

Or setting constraints after creating the table is with Alter Table command.

Suppose there exists a table employee,to set a constraint to it,give

ALTER TABLE Persons
ADD UNIQUE (P_Id);

                                        RELATIONSHIP BETWEEN TABLES.

 you create your table you should set up relationships between the various tables.The foreign key is also used in this issue in some cases.

Master tables are the tables which does the main functionality in a database.First they have to be identified.

When there is a many-many relationship between the various master tables it is better you create an association or relationship table in between them.Association table can have one field each from the 2 tables relating it ,thus creating an association reducing redundancy.



- Sourcebits University
Cloud Computing
www.sourcebits.com