Digital Wallet Solution Database Design

Digital Wallet Solution Database Design

About the System

Technology’s utility has been simplified in every field or facet of human life where it has relieved individuals of the burden of doing acts that require effort and inconvenience. The capstone project, entitled “Digital Wallet Solution” is a digital platform that streamlines financial transactions. The user can store funds, digitally pay goods and services and other financial transactions.

Individuals used to pay for goods and services using traditional cash-based transactions before the advent of technology. All financial transactions must be completed by a human in the traditional method, which requires the usage of cash. Payments and other financial transactions are also manually recorded for future reference, which is prone to errors and might be misplaced and difficult to locate. The manual technique is thought to be ineffective because it takes too much time and effort and leaves a lot of space for error. Because manual payment and other financial operations involve money, consumers require a reliable and secure platform to store funds and expedite financial activities.

In the digital age, a digital wallet is a safe online account where you can save your payment details. It can be used to make purchases on websites, in mobile applications, and in real stores, among other things.

If you make payments online or in-store, a digital wallet solution can make it easier and more secure for you to complete your transactions. It can also assist you in keeping track of your expenditures and managing your finances more effectively.

The ability to save your money in a range of currencies and languages is provided by some digital wallet solutions. They can also assist you with keeping track of your spending and implementing security measures such as two-factor authentication (for further security).

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

tbl_member – this table will hold the information of the members in the system.

• member_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• first_name – the first name of the member
• middle_name – the middle name of the member
• last_name – the last name of the member
• email_address- the member’s email address
• country – the country which the member live in
• contact_number – contact number of the member preferably cellphone or mobile number
• username – preferred username of the member, combined with the password to login to the system
• password – preferred password of the member also used to login to his/her account
• account_status – (0)pending, (1)approved, (2)canceled
• processed_by – it refers to the user who managed and processed the registration of the member.

Create SQL Statement – the statement below is used to create the tbl_member, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_member` (
`member_id` int(11) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email_address` varchar(50) NOT NULL,
`country` varchar(30) NOT NULL,
`contact_number` varchar(15) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`processed_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – the user’s information will be stored in this table.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• username – the preferred username of the username also used for logging in.
• password- the preferred password of the user combined with the username to login to the system.
• complete_name – the complete name of the user
• email_address – the email address of the user

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`complete_name` varchar(100) NOT NULL,
`email_address` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_currency_supported – this table will store the information of the currencies supported by the system.

• currency_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• currency_name – name of the currency
• currency_symbol – the symbol for the specific currency
• usd_equivalent – equivalent to US dollars
• status – (0)active, (1)inactive

Create SQL Statement – the statement below is used to create the tbl_currency_supported, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_currency_supported` (
`currency_id` int(11) NOT NULL,
`currency_name` varchar(20) NOT NULL,
`currency_symbol` varchar(5) NOT NULL,
`usd_equivalent` float NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_gateway – this table will store the information of the gateways available in the system.

• gateway_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• gateway_name – name of the gateways (ph, gcash, paymaya, paypal)
• type – automatic, manual
• status – (0)active, (1)inactive

Create SQL Statement – the statement below is used to create the tbl_gateway, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_gateway` (
`gateway_id` int(11) NOT NULL,
`gateway_name` varchar(30) NOT NULL,
`type` int(1) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_withdrawal – this table will store the information of the withdrawals in the system.

• deposit_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• transaction_code – unique code for the transaction
• member_id – this is foreign key that points out to the member
• amount – amount withdrawn
• charged – charge fee for the withdrawal
• to_receive – amount to be received by whom
• date_time – date and time of the withdrawal
• method – bank transfer, paypal, etc
• status – (0)pending, (1)successful, (2)rejected
• remarks – additional information about the withdrawal

Create SQL Statement – the statement below is used to create the tbl_withdrawal, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_withdrawal` (
`deposit_id` int(11) NOT NULL,
`transaction_code` varchar(30) NOT NULL,
`member_id` int(11) NOT NULL,
`amount` float NOT NULL,
`charged` float NOT NULL,
`to_receive` float NOT NULL,
`date_time` datetime NOT NULL,
`method` int(1) NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_deposit – the details of the deposit transactions made in the system.

• withdrawal_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• transaction_code – unique code for the transaction
• member_id- foreign key that points out to the member
• deposit_amount – amount deposited
• currency_id – this is a foreign key that points out to the currency
• date_time – date and time of deposit
• payment_gateway_id – this is a foreign key that points out to the payment gateway used
• status – (0)pending, (1)successful, (2)rejected
• remarks – additional information about the deposit transaction

Create SQL Statement – the statement below is used to create the tbl_deposit, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_deposit` (
`withdrawal_id` int(11) NOT NULL,
`transaction_code` varchar(30) NOT NULL,
`member_id` int(11) NOT NULL,
`deposit_amount` float NOT NULL,
`currency_id` int(11) NOT NULL,
`date_time` datetime NOT NULL,
`payment_gateway_id` int(11) NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_transaction_log – this table will hold the information of the transaction logs.

• log_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• member_id – this is a foreign key that points out to the member
• type – (0)deposit, (1)withdrawal
• amount – amount deposited or withdrawn
• status – (0)pending, (1)successful, (2)rejected

Create SQL Statement – the statement below is used to create the tbl_transaction_log, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_transaction_log` (
`log_id` int(11) NOT NULL,
`member_id` int(11) NOT NULL,
`type` int(1) NOT NULL,
`amount` float NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_transaction_setting – the transaction setting’s details will be stored in this table.

• setting_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• usd_current_value – the current value of US dollar
• withdrawal_charge – fee for withdrawal
• daily_withdrawal_limit – amount of the daily withdrawal limit
• monthly_withdrawal_limit – amount of the monthly withdrawal limit

Create SQL Statement – the statement below is used to create the tbl_transaction_setting, copy the sql statement and paste it in the sql manager/tab of your phpmyadmin.

CREATE TABLE `tbl_transaction_setting` (
`setting_id` int(11) NOT NULL,
`usd_current_value` float NOT NULL,
`withdrawal_charge` float NOT NULL,
`daily_withdrawal_limit` float NOT NULL,
`monthly_withdrawal_limit` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

For this content, we have discussed and enumerated the possible database tables and their respective columns or fields for the system of Digital Wallet Solution

Please watch the video tutorial on how to prepare and create the different tables of the database of Digital Wallet Solution.

Video Tutorial

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive  free capstone projects resources and computer programming tutorials.

Hire our team to do the project.

Related Topics and Articles:

Digital Wallet Solution Free Download Bootstrap Template Source code

IPO Model Conceptual Framework of Digital Wallet Solution

QR Code Digital Vaccine Certification Capstone Project

Social Media Control and Advance Monitoring using Digital Parenting

Digital School Management