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

12 thoughts on “Sistas and Brothas Guide to Creating Their First Account Provider Database

  1. darr741,

    copy and paste the example above the comment sections – the last part of this article that begins with the following line into a new Query Analyzer button. Highlight one line such as –CreateAccount ‘John Doe’,28.98– and press the execute button to create data, such as the account, go through the whole script and you will see data populated and returned.

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

    –create some accounts
    CreateAccount ‘John Doe’,28.98
    CreateAccount ‘Jane Doe’,50.00

  2. Ok Thanks, that worked… the only thing i think should be left out is

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

    It removes all the data from the table

  3. Ed,

    I know a little about databases, here is the sql code if you wanted to see all the data with the tables joined on the accountid key and the query filters on account id 1. It will get the data just as the RetrieveAccount1 but it will also show the name.

    SELECT GO.TransferID, GO.AccountID, GO.TransferType, GO.TransferAmount, GO.TransferDate, GO.AccountBalance, T_Accounts.AccountName,
    T_Accounts.AccountID AS Expr1
    FROM T_Transfers AS GO INNER JOIN
    T_Accounts ON GO.AccountID = T_Accounts.AccountID
    WHERE (GO.AccountID = ‘1’)

    I really would like to have an online conversation with someone a lot of stuff that i’m behind on and just to network and learn from each other.

  4. Ed,
    I really thank you for this website and encouraging us to use technology and enhance our skillset.

    Darryl

  5. @darr741

    When providing billing data or account statements, there is a header section at the top and then their is a detail section at the bottom, like your phone bill or your bank statement. So you do not need to incorporate the name from the Accounts table into the Transfers table.

    We design code to work at the functional/task level so when you write the procedure out, it looks as simple as this:

    RetrieveAccountHeader ‘John Doe’,’11/2012′
    RetrieveAccountActivity ‘John Doe’,’11/2012′

    So let’s say you are operating a mobile payment service in Latin America and they are using older phone models. You have one screen that can display account header and have an option such as “View Activity” that calls the activity – this is how we should design code to separate out functions that can be reused efficiently.

    You will rarely see, if at all us using any code that pulls back all of the data in one shot – we break it down so it can be reused and modular to increase scalability.

  6. darr741 you can reach me through the admin side,or by going to the answers section and sending an email to the address of the user name that starts with “g” its a gmail account. “The Ninja” is a new user name for the huslte I’m on that brother Ed put us down with a few months back.

    @Ed…lol no need for the videos…we hear you this is gonna be fyah

Comments are closed.