problem with depositing and withdrawing cash in a banking system

heshan · June 13, 2011

Hi all,

I have a small problem relating to my project. I want to do some cash transactions (deposits / withdrawals)

Account numbers are already created and they were stored in my customer table. i want to deposit or withdraw cash to/ from these accounts.

I am not sure with my current table structure in my database.

customer (account_number, nic, full_name, name_with_initials,phone_number, address, gender, date_of_birth, account_type, fd_period, account_balance)

transaction (tran_id, account_number, account_type, account_balance, transaction_type, transaction_amount, transaction_date)

account (account_id, account_number, full_name, account_type, account_balance, account_interest)

Herewith i attached my current php files relevant to this scenario. Looking for a good response from anyone......

[attachment deleted by admin]

ignace · June 13, 2011

account_number = account_id? Clearly customer and account are one and the same yet they share account_type and account_balance? And for some reason transaction has the same information?

If one customer can have multiple accounts and that depends on your Business Requirements then the DB design should be something like:

customer (customer_id, ..)
account (account_id, customer_id, ..)

If the customer and the account are the same thing then drop one of both and merge the columns with the other (or create an account_detail table).

account (account_id, account_type_id, balance)
account_detail (account_id, nic, full_name, initial, phone_number, address, gender, dob)

This doesn't pass Normal Form 2 since not all data is atomic: full_name, phone_number, and address. I left interest_rate and fd_period out because I don't know what fd_period means and interest_rate seems to me something about the account_type_id but I'm not sure about this since I don't know anything about your actual Business Requirements. Transaction table then looks like:

transaction (transaction_id, transaction_type_id, from_account_id, to_account_id, amount, log_date)

heshan · June 14, 2011

Thanks a lot for your comments.. I should elaborate more about my business requirements.

In my Accounts opening form, there are seperate fields for enter the customer details.(nic, full_name, name_with_initials, phone_number, address, gender, date_of_birth, account_type, fd_period)

I have inserted fd_period because if a customer wants to open a fixed deposit he should be able to select the desired time period.(1year, 2years etc..)

I did not create a customer_ id because i want to create account numbers. Therefore if i put customer_id in my customer table it distracts my purpose. When the user entered all the customer data and click on submit button then an account number 1 should be created. I already did this part.

Regarding the account_interest, the manager should be able to run a query and adds interets to the accounts seperately at the end of the month.

In your transaction table it defined field like from_account_id and to_account_id. Can you explain for what it is? Is it for deposit and withdrawals?

How can i choose my best table structure?

heshan · June 16, 2011

Hi,

Since i am new to the subject, I want some help here....

According to the comments made by ignace i have created my table structure as follows.

account_details (account_number, nic, full_name, name_with_initials, phone_number, address, gender, date_of_birth)

account (account_number, account_type, fd_period, account_balance, account_interest)

transaction (tran_id, transaction_type, from_account, to_account, transaction_amount, transaction_date)

I want following things to be done.

When clicks on submit button, the name of the customer should be displayed with "transaction has completed successfully".
If a customer wants to withdraw an amount more than the available amount his account a message should be displayed as " Insufficient balance"
If the user entered an incorrect account number, a message should be displayed as "Invalid account number"

The 4 pages are as follows.

NOTE : An Accounts opening officer is responsible for the opening of accounts and a teller's duty to deposit or withdraw cash from/to accounts.

open_account.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script src="datetimepicker_css.js">
  </script>  
<script LANGUAGE="JavaScript">
function fd_show(val){
if(val=="fd"){
   document.getElementById("fd_box").style.visibility = 'visible';
  }else{
   document.getElementById("fd_box").style.visibility = 'hidden';
  }
}
</script>
<script type="text/javascript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
  eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'");
  if (restore) selObj.selectedIndex=0;
}
//-->
</script>  
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
#apDiv1 {
   position:absolute;
   width:177px;
   height:55px;
   z-index:1;
   left: 12px;
   top: 171px;
}
-->
</style>
</head>
<body>
<h2><img src="../images/image 1.jpg" alt="" width="118" height="89" /> <img src="../images/logo-default.jpg" alt="" width="350" height="89" /> <img src="../images/image 2.jpg" alt="" width="100" height="89" /></h2>
<h2> </h2>
<h2> </h2>
<h2>Accounts Opening Form </h2>
<p> </p>
<?php if(isset($_POST['run'])){include_once "./handlers/account.php";} ?>
<form action="open_account.php" name="form1" method="post">
<input type="hidden" name="run" value="yes" />
  <fieldset>
  <legend class="cap">Customer details</legend>
  <table width="75%" border="0" cellspacing="0" cellpadding="5" align="center">
                 <tr>
                  <td> </td>
                  <td class="title02"> </td>
                  <td> </td>
                  <td> </td>
                 </tr>
                                          
                 <tr height="30">
                  <td width="10%"> </td>


                  <td width="25%" class="title02" align="left">NIC</td>
                  <td width="55%" class="attribute1" align="left"><label>
                    <input type="text" name="nic" id="textfield" />
                  </label></td>
                  <td width="10%"> </td>
                 </tr>
                 <tr height="30">
                  <td> </td>
                        <td width="25%" class="title02" align="left">Full name</td>
                    <td width="55%" class="attribute1" align="left"><label>
              <input type="text" name="full_name" class="attribute1" />
              </label></td>
                  <td width="10%"> </td>
                 </tr>
                 <tr height="30">
                  <td> </td>
                  <td class="title02" align="left">Name with initials</td>
                  <td class="attribute1" align="left"><input type="text" name="name_with_initials" class="attribute1" /></td>
                 </tr>
                   <tr height="30">
                  <td width="10%"> </td>


                  <td width="25%" class="title02" align="left">Phone Number</td>
                  <td width="55%" class="attribute1" align="left"><label>
                  <input type="text" name="phone_number" class="attribute1" />
                  </label></td>
                  <td width="10%"> </td>
                 </tr>
                       <tr height="30">
                  <td width="10%"> </td>


                  <td width="25%" class="title02" align="left">Address</td>
                  <td width="55%" class="attribute1" align="left"><label>
                    <textarea name="address" id="textarea" cols="45" rows="5"></textarea>
                  </label></td>
                  <td width="10%"> </td> 
    <tr height="30">
                        
                  <td> </td>
                  <td class="title02" align="left">Gender</td>
                  <td class="attribute1" align="left"><label>
                    <select name="gender" id="select">
                           <option selected="selected"></option>
                      <option value="male">Male</option>
                            <option value="female">Female</option>
                     </select>
                  </label></td>
    <tr height="30">
                  <td> </td>
                  <td class="title02" align="left">Date of birth</td>
                  <td class="attribute1" align="left"><input type="Text" id="demo3" name="date_of_birth" maxlength="25" size="25"/><img src="../images/cal.gif" onClick="javascript:NewCssCal('demo3','yyyyMMdd')" style="cursor:pointer"/> </td>
    </tr>
     
      
      <tr height="30">
        <td> </td>
        <td width="25%" class="title02" align="left">Account Type</td>
        <td width="55%" align="left" bgcolor="#FFFFFF" class="attribute1">
        <select name="account_type" onChange="fd_show(this.value)">
            <option selected="selected"></option>
            <option value="savings_investment">Savings Investment</option>
            <option value="shakthi" >Shakthi</option>
            <option value="surathal">Surathal</option>
            <option value="abhimani_plus">Abhimani Plus</option>
            <option value="yasasa">Yasasa Certificates</option>
            <option value="fd">Fixed Deposits</option>
          </select> </td>
     
      <tr height="30">
        <td colspan="4">
            <div id="fd_box" style="visibility: hidden;">
            <table width="100%" border="0" cellspacing="0" cellpadding="5" align="center">
            <tr height="30">
                   
            </tr>
            <tr height="30">
              <td width="10%"> </td>
              <td width="25%" class="title02" align="left">FD period</td>
              <td width="55%" class="attribute1" align="left"><select name="fd_period">
                 <option selected="selected"></option>
                  <option value="< 1">less than 1 year</option>
                  <option value="1-3 years" >1-3 years</option>
                  <option value="> 3">more than 3 years</option>
                  <option value="il">immediate loan</option>
                </select></td>
              <td width="10%"> </td>
            </tr>
            </table>
            </td>
      </tr>
  </table>
    <p align="center"> </p>
    <p align="center">
      <input type="submit" onClick="return Validate();" name="submit" value="Submit" class="attribute1" />
        
      <input type="reset" name="reset" value="Reset" class="attribute1" />
        
      <label>
        <input type="submit" name="button2" id="button2" value="Help" />
      </label>
    </p>
  </fieldset>
  </td>
  <td width="5%"> </td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td align="center"> </td>
    <td> </td>
  </tr>
  <tr>
    <td> </td>
    <td><font color="red" size="1" ></font></td>
    <td> </td>
  </tr>
                      
                      
                 
                      
  </table>
<p align="center">    </p>
  </fieldset>
</td>
            <td width="5%"> </td>
         </tr>


           <tr>


             <td> </td>
             <td> </td>
             <td> </td>
  </tr>
           
           
           
           <tr>
             <td> </td>
             <td align="center"> </td>
             <td> </td>
  </tr>
           <tr>
             <td> </td>
                            <td><font color=red size="1" ></font></td>
             <td> </td>
  </tr>
         </table>
    
</form>


<script language = "Javascript">
  
function Validate()
{
    if (document.form1.nic.value == '') 
    {
        alert('Please fill in nic!');
        return false;
    }
   if (document.form1.full_name.value == '') 
    {
        alert('Please fill in full name!');
        return false;
    }
    if (document.form1.name_with_initials.value == '') 
    {
       alert('Please fill in name with initials!');
       return false;
    }
   if (document.form1.phone_number.value == '') 
    {
        alert('Please fill in phone number!');
        return false;
    }
   if (document.form1.address.value == '') 
    {
        alert('Please fill in address!');
        return false;
    }
    if (document.form1.gender.value == '') 
    {
        alert('Please fill in gender!');
        return false;
    }
    if (document.form1.date_of_birth.value == '') 
    {
       alert('Please fill in date_of_birth!');
      return false;
    }

if (document.form1.account_type.value == '') 
    {
       alert('Please fill in type of account!');
      return false;
    }
   
    return true;
}
</script>
        
</body>
</html>

account.php

<form id="form1" name="form1" method="post" action="">
  <label>
  <input type="submit" name="button" id="button" value="Home" />
  </label>
</form>
<p>   </p>
<p>
  <?php
$connect=mysql_connect('localhost','root','');
mysql_select_db('bank',$connect);
//Create Array
$info = array(
           'nic' => $_POST["nic"],
           'full_name' => $_POST["full_name"],
           'name_with_initials' => $_POST["name_with_initials"],
           'phone_number' => $_POST["phone_number"],
           'address' => $_POST["address"],
           'gender' => $_POST["gender"],
           'date_of_birth' => $_POST["date_of_birth"],
	   'account_type' => $_POST["account_type"],
	   'fd_period' => $_POST["fd_period"]
                    
           );
//Prepare the Insert Query
$insert_query = "INSERT INTO account_details (
               nic,
               full_name,
               name_with_initials,
               phone_number,
               address,
               gender,               
               date_of_birth
               ) 
               VALUES
               (
               '$info[nic]', 
               '$info[full_name]', 
               '$info[name_with_initials]', 
               '$info[phone_number]', 
               '$info[address]', 
               '$info[gender]', 
		   '$info[date_of_birth]'
               
               )";
//Run a switch on the chosen type of account
if($info['account_type'] == "abhimani_plus") {
      if($info['gender']!="female") {
         //Show error messages here
         echo "You do not meet the critera required to open this account.";exit;
      }
   }
//Account Creation Here
$r = mysql_query($insert_query);
if($r) {
echo "A new account with number ".mysql_insert_id()." has been created successfully.";die();
}
?>
</p>
<p> </p>
<p> </p>

cash_transactions.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script src="datetimepicker_css.js">
  </script>  
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
#apDiv1 {
position:absolute;
width:507px;
height:57px;
z-index:1;
left: 218px;
top: 595px;
}
body,td,th {
font-size: 18px;
color: #000;
font-weight: bold;
}
h1 {
color: #CCC;
}
#apDiv2 {
position:absolute;
width:223px;
height:49px;
z-index:2;
left: 10px;
top: 244px;
color: #F00;
}
-->
</style>
</head>

<body text="#FF0000">
<p><img src="../images/image 1.jpg" width="118" height="89" />
  <img src="../images/logo-default.jpg" width="350" height="89" />
<img src="../images/image 2.jpg" width="100" height="89" /></p>
<p> </p>
<form id="form2" name="form2" method="post" action="logout.php">
  <label>
    <input type="submit" name="button4" id="button4" value="Logout" />
  </label>
</form>
<p>  </p>
<p> </p>
<p> </p>
<p> </p>
<h2>Cash Deposits / Withdrawals</h2>
<p> </p>
<form name="form1" method="post" action="transactions.php" >

    
			  
  <fieldset>
  <legend class="cap">Transaction details</legend>
  <p class="cap"> </p>
  <table width="75%" border="0" cellspacing="0" cellpadding="5" align="center">
				  
                                          
				  <tr height="30">
					<td width="10%"> </td>

					<td width="25%" class="title02" align="left">Account Number</td>
					<td width="55%" class="attribute1" align="left"><label>
					  <input type="text" name="account_number" id="textfield" />
					</label></td>
					<td width="10%"> </td>
				  </tr>
				  
<tr height="30">
					<td> </td>
					<td class="title02" align="left">Transaction Type</td>
					<td class="attribute1" align="left"><select name="transaction_type" id="select">
                          <option selected="selected"></option>
                          <option value="deposits">Deposits</option>
                          <option value="withdrawels">Withdrawels</option>
                                                                                                                                                </select></td>
    </tr>
			       <tr height="30">
					<td width="10%"> </td>

					<td width="25%" class="title02" align="left">Transaction Amount</td>
					<td width="55%" class="attribute1" align="left"><label>
					<input type="text" name="transaction_amount" class="attribute1" />
					</label></td>
                        
                        
                        
					<td width="10%"> </td>
				  </tr>
                      
                      <tr height="30">
					<td width="10%"> </td>

					<td width="25%" class="title02" align="left">Transaction Date</td>
					<td width="55%" class="attribute1" align="left"><label>
					<input type="text" id="demo3" name="transaction_date" class="attribute1" />
					<img src="../images/cal.gif" onClick="javascript:NewCssCal('demo3','yyyyMMdd')" style="cursor:pointer"/></label></td>
                        
                        
                        
					<td width="10%"> </td>
				  </tr>
                       <tr height="30">

	  
                      
  </table>
  
  <p align="center">
				   <input type="submit" name="submit" value="Submit" class="attribute1" onClick="return Validate();"/> 
                           
<input type="reset" name="reset" value="Reset" class="attribute1" />
				</p>
  </fieldset>
<script language = "Javascript">
  
function Validate()
{
    if (document.cash_deposit.textfield.value == '') 
    {
        alert('Please insert the cash amount!');
        return false;
    }
    if (document.cash_deposit.textfield2.value == '') 
    {
       alert('Please enter the account number !');
       return false;
    }

    return true;
}
</script>
<p>  </p>
</body>
</html>

transactions.php

<?php

$connect=mysql_connect("localhost","root","");
mysql_select_db("bank",$connect) or die ("could not select database");

if(isset($_POST['submit'])){
      $query= "SELECT `full_name` FROM account_details WHERE `account_number`='".$_POST['account_number']."'";
      $result = mysql_query($query) or die(mysql_error());
      $row = mysql_fetch_assoc($result);
      if(mysql_num_rows($result)==1 and $row['account_balance']<$_POST['transaction_amount'] and strtolower($_POST['transaction_type'])=="withdrawal"){
          echo "Insufficient balance";
      }elseif(mysql_num_rows($result)==1){

      if(strtolower($_POST['transaction_type'])=="deposit"){
            $operator = "+";
      }else{
            $operator = "-";
      }
      $query= "UPDATE account SET `account_balance`=(`account_balance`".$operator.$_POST['transaction_amount'].") WHERE `account_number`='".$_POST['account_number']."'";
      mysql_query($query) or die(mysql_error());
   
      $query = "INSERT INTO  transaction (account_number, transaction_type, transaction_amount, transaction_date) VALUES('".$_POST[                'account_number']."','".$_POST['transaction_type']."','".$_POST['transaction_amount']."','".$_POST[                'transaction_date']."')";
  
mysql_query($query) or die(mysql_error());
            echo $row['full_name'].",<br> your transaction has been successfully processed on ";
      }else{
            echo "invalid account number";
      }
}

?>

Sign In

problem with depositing and withdrawing cash in a banking system

Recommended Posts

heshan

Link to comment

Share on other sites

ignace

Link to comment

Share on other sites

heshan

Link to comment

Share on other sites

heshan

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information