Sistas and Brothas Guide to Creating Their First Account Provider Database

This article is design for African-Americans to learn how to create databases and work with database technology and the first example we are going to do is an account provider service. The goal of this article is to show cats how to copy and paste scripts and learn how to expand on frameworks provided to them to self-empower themselves to create side hustles using technology solutions.

Account Provider Database

An account provider database will store account and account activity for customers that include deposits and withdrawals as well as transfers. This service is a core component for next generation hustles that enable brothas and sistas to offer stored value capability of funds that can be spent within their realm of business.

There are multiple uses of an account provider that brothas and sistas can create and incorporate into their side hustles. Let’s cover a few:

Mobile Payments. Enable brothas and sistas to send payments to each other similar to PayPal by transferring funds from their account to another account and keep track of their deposits, withdrawals and transfers. Allow brothas and sistas view their account activity on their mobile device in real-time.

Prepaid Spending. Create stored value accounts for retail businesses or pop-up events and allow customers to spend and track their activity. Think of Chuck E Cheese or Dave & Buster’s where you have to load up a prepaid card to play games and activities. Or think of a Laundromat where you load up a account to use the washer and dryer.

Online Accounts. This is where you have a web site and users set up an account to spend as needed. For example, you setup an internet business for South Africa population for a ringtone web site featuring South African music artists. Cats in South Africa load up their mobile account with 200 rand and spend from the prepaid account when they want to download new ringtones.

Online Games. Keep in mind that you can use this to do games like those games on Facebook or even the RPG games. I can load money, buy a dragon slaying sword for my avatar and transfer it to another avatar online and buy a bow and arrow from them and keep track of the activity and spending.

Gamification. We can provide gamification that shows activity for receiving rewards and badges and account bonus and purchases. For example, the upcoming retail for the hood point of sale application allow employees to have stored value accounts that rewards them when they accomplish something like perfect attendance for a month or meet or exceed a KPI.

So as you see, there are plenty of uses for stored valued accounts that can create a whole new slew of technology based hustles brothas and sistas can do on the side or passively. But we have to learn how to create the framework and foundation from scratch so we can build our own and build up and improve from our own work to bigger and better things.

Create New Database

If you downloaded SQL Server Management Studio with the SQL 2008 R2 Express from the http://www.microsoft.com/sqlserver/en/us/editions/previous-versions.aspx URL and installed it, then you can just open it up. Please note that I do not have time to help you download and install this – I believe it is self-explanatory. Go on YouTube if you have to learn it, don’t put it on me to teach you stuff like this because I have to focus on the real mission.

Also the reason I’m asking you do SQL 2008 R2 is because this is the version being used on the GoDaddy hosted site. If you download SQL 2012 and I haven’t, you may have to change something where the script has to be compatible with 2008, someone can correct me on this.

So all you have to do is right click on the database folder and select “Create New Database” and just enter the name of the database – call it AccountProvider” and there you go, it’s created! Now right click on the new database you created and there should be a “New Query” selection and that should create

Run the Script

Copy and paste the script at the end of this article in the Query Window. Now, make sure the drop down box says the name of the database you just created like it says “AccountProvider” and not “master” – the reason why is I want you to make sure you are running the script in the database you created, not the main or “master” database in the dropdown.

Do not highlight lines of the script and make sure the mouse cursor is on the first line. When you just press the “Execute” button, the script should create all of the tables and stored procedures for you. That’s all you need to do to establish your first database by just copying, pasting and execute. That’s how I want you to start practicing so you can be up and running when we hand down future scripts for you to run other side hustles.

Run the Example Stored Procedures

Now, delete all of the code from the Query window or create a new query window by clicking on the “New Query” button in the menu bar at the top left section and paste the scripts provided below. These are scripts to show you how the account provider database works.

All you have to do is select a line of code like I did in the picture above and then press “Execute” to execute that specific line. Start from the top line to the bottom to add accounts, conduct a transfer and view account activity. In the picture example above, I executed a line of code that retrieve the account activity and you should see a description and a running balance.

The point I want to show you here is these codes is what you are later going to use in the ASP.NET web application to send these commands to the AccountProvider database to retrieve data to display. So that is later where you create the web site for mobile phones to create their own account, deposit funds, view their balance, transfer to another account holder and request a withdrawal.

Play With the Code

Now that you see a real working example of the database creating account, transferring funds and recording activities, the next step is for you to study the code underneath. I want you to look at the tables on the database and how they are designed. Look at the stored procedures and see how the id fields are generated and how I’m referencing the id from one table to another table to keep the records in sync.

By no means this is a professional grade Account Provider database for you to post online and start a hustle. It still need to be refined and we already done that – this code is to help you learn how to create and work a database. Keep in mind how important it is for you to learn and going to have to build these types of hustles from scratch. For example, if you need to add the capability to send a low balance alert when someone balance drops below $20, you need to know how to put that in your code and it ain’t hard to do.

For too long, African-Americans been sitting around trying to “look the part” about being in this tech game but can’t program worth a damn to bring something real to the table. In addition, there are a whole horizon of hustles brothas and sistas can create with technology if we knew how to program code and create databases and it ain’t as hard as cats want to make it out to be. So we hope you take some serious time to yourself, turn off the damn television and practice this database stuff so we can get you ready for prime-time to do for self, your people and your future.

*************Create Database Script*************


/****** Object: Table [dbo].[T_Transfers] Script Date: 10/29/2012 20:02:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Transfers](
[TransferID] [bigint] IDENTITY(1,1) NOT NULL,
[AccountID] [bigint] NULL,
[TransferType] [nvarchar](50) NULL,
[TransferAmount] [decimal](18, 2) NULL,
[TransferDate] [datetime] NULL,
[AccountBalance] [decimal](18, 2) NULL,
CONSTRAINT [PK_T_Transfers] PRIMARY KEY CLUSTERED
(
[TransferID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_Accounts] Script Date: 10/29/2012 20:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_Accounts](
[AccountID] [bigint] IDENTITY(1,1) NOT NULL,
[AccountName] [nvarchar](max) NULL,
[Balance] [decimal](18, 2) NULL,
CONSTRAINT [PK_T_Accounts] PRIMARY KEY CLUSTERED
(
[AccountID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[CreateAccount] Script Date: 10/29/2012 20:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CreateAccount]
@accountname nvarchar(max),
@balance decimal(18,2)
as
begin transaction
insert t_accounts
(accountname,balance)
values
(@accountname,@balance)
if @@error <> 0
begin
rollback
return
end
Commit
GO
/****** Object: StoredProcedure [dbo].[RetrieveAccount] Script Date: 10/29/2012 20:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RetrieveAccount]
@accountid bigint
as
Select * from t_transfers where accountid = @accountid order by transferdate desc
GO
/****** Object: StoredProcedure [dbo].[InsertTransfer] Script Date: 10/29/2012 20:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[InsertTransfer]
@accountid bigint,
@transfertype nvarchar(50),
@amount decimal(18,2),
@balance decimal(18,2)
as
begin transaction
insert t_transfers
(accountid,transfertype,transferamount,transferdate,accountbalance)
values
(@accountid,@transfertype,@amount,getdate(),@balance)
if @@error <> 0
begin
rollback
return
end
Commit
GO
/****** Object: StoredProcedure [dbo].[DebitAccount] Script Date: 10/29/2012 20:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[DebitAccount]
@accountid bigint,
@amount decimal(18,2)
as
begin transaction
update t_accounts
set balance = (balance - @amount)
where accountid = @accountid

declare @newbalance decimal(18,2)
select @newbalance = balance from t_accounts where accountid = @accountid

exec InsertTransfer @accountid,'Debit',@amount,@newbalance

if @@error <> 0
begin
rollback
return
end
Commit
GO
/****** Object: StoredProcedure [dbo].[CreditAccount] Script Date: 10/29/2012 20:02:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CreditAccount]
@accountid bigint,
@amount decimal(18,2)
as
begin transaction
update t_accounts
set balance = (balance + @amount)
where accountid = @accountid

declare @newbalance decimal(18,2)
select @newbalance = balance from t_accounts where accountid = @accountid

exec InsertTransfer @accountid,'Credit',@amount,@newbalance

if @@error <> 0
begin
rollback
return
end
Commit
GO

*************Run the Example Script*************


--create some accounts
CreateAccount 'John Doe',28.98
CreateAccount 'Jane Doe',50.00

--view the accounts
select * from t_accounts

--process some debits and credits
CreditAccount 1,34.67
DebitAccount 1,.65

--process transfer of $5 from one account to another
DebitAccount 1,5.00
CreditAccount 2,5.00

--look at account activity
RetrieveAccount 1
RetrieveAccount 2

--reset all the tables to empty
truncate table t_accounts
truncate table t_transfers

Leave a Reply