The database is similar to my personal wallet and is designed for 10 altcoins, but can be easily changed for different coins and needs. It serves as a tool to keep track of cryptocurrency transactions and holdings. The code includes table structures for storing transaction information, including the transaction hash, date, amount, and type.
Of course, there are better ways to track currencies, such as Coin Gecko or Coinmarketcap, but it’s nice to have your own design. I enjoyed creating this project.
If you’re happy with my work, feel free to take the code in full or in parts to use for your own needs.
My most transaction is made on Binance but I’m also using Crypto.com, Coinbase, and Huobi. For now, let’s assume we are going to use only these four most popular cryptocurrency exchanges. If you’re using other exchanges, you can add your own symbols for your own needs. Just remember to change the constraints for the column. Be aware that the column unique identifier is a 16-byte binary SQL Server data type, so it can take up space if you’re planning a lot of transactions. However, it can be very helpful in identifying transactions.
Why did I use a UNIQUE NONCLUSTERED INDEX?
A UNIQUE constraint only allows one null in the column, so it is not suitable for my needs. I want the column Transaction_Hash to be a nullable column because not everybody needs the transaction hash. However, if you decide to insert the hash, the Transaction_Hash key has to be unique without duplicates, as in crypto transactions, there is no way for the transaction keys to be duplicated. Here is the website to track crypto transactions >>> www.blockchair.com
Transaction_Hash is used to track transactions on the blockchain, like cryptocurrency exchanges to wallets, wallet to wallet, or miner to a wallet.
Why am I using SEQUENCE?
A SEQUENCE will give me an ID independently, no matter which table I insert the transaction. That means I can easily track which coin I bought first its easy how much money I spend on crypto weekly, monthly, quarterly and yearly. All tables use the same SEQUENCE (newSeq).*
Symbols :
B – Binance
C – Coinbase
CR – Crypto.com
H – Huobi
Content of Database :
CREATE DATABASE
CREATE SEQUENCE
CREATE TABLE x 10
CREATE PROCEDURE
CREATE VIEW x 2
CREATE FUNCTION
TESTING
a few ways to introduce data:
OVER ( PARTITION BY [Name] ORDER BY Sum_tran
GROUP BY ROLLUP
WITH CTE_PIVOT AS
PIVOT
There is no need to create indexes on such a small database!
Download the full query here >>>>>>> Project_crypto
or
see at GitHub >>> https://github.com/jacenty79/Crypto_project_jacenty.net/blob/main/Crypto_Project_jacek79.net.sql
CODE :
CREATE DATABASE Project_Crypto_tracker USE Project_Crypto_tracker CREATE SEQUENCE newSeq AS BIGINT START WITH 1 INCREMENT BY 1 MINVALUE 1 GO --ALTER SEQUENCE newSeq RESTART WITH 1 -- to restart sequence if needed ! --================================== Patern for additional coin ==================== replace all {/} /* CREATE TABLE {/} ( [Id_Transaction] int not null PRIMARY KEY , [Name] nvarchar (30) not null , [Symbol] char(10) null , [Date_time] datetime not null , [Quantity] decimal(20,4) null , [Current_USD] money null , [Trans_type] char(3) not null , [Sum_tran] as cast((Quantity*Current_USD) as decimal(8,2) ) , [Unq_nr] uniqueidentifier not null , [Transaction_Hash] nvarchar(300) null ) ALTER TABLE {/} ADD CONSTRAINT [Chk_Symbol_UP_{/}] CHECK (Symbol = upper('{/}')) ALTER TABLE {/} ADD CONSTRAINT [Df_Date_time_{/}] DEFAULT (getdate()) FOR Date_time ALTER TABLE {/} ADD CONSTRAINT [Df_Trans_type_{/}] DEFAULT ('B') FOR Trans_type ALTER TABLE {/} ADD CONSTRAINT [Chk_Trans_type_{/}] CHECK ( Trans_type = upper ('B') or Trans_type = upper ('C') or Trans_type = upper ('CR') or Trans_type = upper ('H') ) ALTER TABLE {/} ADD CONSTRAINT [Df_Unq_nr_{/}] DEFAULT NEWSEQUENTIALID() FOR Unq_nr ALTER TABLE {/} ADD CONSTRAINT [Df_defaultName_{/}] DEFAULT ('{/}') FOR Name ALTER TABLE {/} ADD CONSTRAINT [Df_sequence_{/}] DEFAULT NEXT VALUE FOR newSeq FOR Id_Transaction CREATE UNIQUE NONCLUSTERED INDEX IND_TEST ON {/} (Transaction_Hash )WHERE Transaction_Hash IS NOT NULL */
There is no need to add all 10 tables on the page I will add only a pattern and one example table,
rest of the tables you will find in the download attachment
IF EXISTS (SELECT * FROM PolcaDot ) DROP TABLE PolcaDot CREATE TABLE PolcaDot ( [Id_Transaction] int not null PRIMARY KEY , [Name] nvarchar (30) not null , [Symbol] char(10) null , [Date_time] datetime not null , [Quantity] decimal(20,4) null , [Current_USD] money null , [Trans_type] char(3) null , [Sum_tran] as cast((Quantity*Current_USD) as decimal(8,2) ) , [Unq_nr] uniqueidentifier not null , [Transaction_Hash] nvarchar(300) null ) ALTER TABLE PolcaDot ADD CONSTRAINT [Chk_Symbol_UP] CHECK (Symbol = upper('DOT')) -- to prevent inserting incorect coins into DOT table ALTER TABLE PolcaDot ADD CONSTRAINT [Df_Date_time] DEFAULT (getdate()) FOR Date_time -- time of inserting into DB ALTER TABLE PolcaDot ADD CONSTRAINT [Df_Trans_type_POL] DEFAULT ('B') FOR Trans_type ALTER TABLE PolcaDot ADD CONSTRAINT [Chk_Trans_type] CHECK ( Trans_type = upper ('B') -- Binance or Trans_type = upper ('C') -- Coinbase or Trans_type = upper ('CR') -- Crypto.com or Trans_type = upper ('H') ) -- Huobi ALTER TABLE PolcaDot ADD CONSTRAINT [Df_Unq_nr] DEFAULT NEWSEQUENTIALID() FOR Unq_nr ALTER TABLE PolcaDot ADD CONSTRAINT [Df_defaultName] DEFAULT ('PolcaDot') FOR Name ALTER TABLE PolcaDot ADD CONSTRAINT [Df_sequence] DEFAULT NEXT VALUE FOR newSeq FOR Id_Transaction -- Using my sequence for Id_transaction -- UNIQUE CONSTRAINT allow only one null in column so UNIQUE NONCLUSTERED INDEX will allow more than one NULL and unique --Transaction_Hash key without duplicates as in crypto transaction there is no way for the transaction keys to be duplicated CREATE UNIQUE NONCLUSTERED INDEX IND_crypto_PolcaDot ON PolcaDot (Transaction_Hash )WHERE Transaction_Hash IS NOT NULL
Creating a procedure for transaction (Insert)
This SP is for quick insert and does not allow changing the date
for inserting with a different date than getdate() please use INSERT statement or UPDATE instead
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'Crypto_INSERT') DROP PROCEDURE Crypto_INSERT GO CREATE PROCEDURE Crypto_INSERT ( @Statement nvarchar(40) = '' , @Quantity decimal(20,4) , @Current_USD money , @Trans_type char(3) = default , @Transaction_Hash nvarchar(300) = default ) AS BEGIN if @Statement = 'LINK' begin Insert into Chainlink (Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash ) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash ) end if @Statement = 'DOT' begin Insert into PolcaDot ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'MATIC' begin Insert into Polygon ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'FTM' begin Insert into Fantom ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'CRO' begin Insert into Cronos ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'JASMY' begin Insert into JasmyCoin ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'GRT' begin Insert into TheGraph ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'BCH' begin Insert into BitcoinCash ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'REEF' begin Insert into Reef ( Symbol , Quantity ,Current_USD , Trans_type,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end if @Statement = 'XVG' begin Insert into Verge ( Symbol , Quantity ,Current_USD , Trans_type ,Transaction_Hash) OUTPUT inserted.* values ( @Statement , @Quantity ,@Current_USD ,@Trans_type , @Transaction_Hash) end print 'Transaction added into DB' if @Statement not in ('XVG','REEF','BCH','GRT','JASMY','LINK','CRO','DOT','FTM') begin select 'Transaction unsuccessful please check symbol of your coin ' as [Information] , CAST(getdate () as nvarchar(20)) as [DateTime] , SYSTEM_USER as [User] end END
Testing buying/selling options, procedure, constraints
######################## TESTING ############################################################################################# -- inserting Insert into PolcaDot ( Symbol , Quantity ,Trans_type ,Current_USD) values ( 'DOT' , 14.5 , 'B' , 186.89 ) ,( 'DOT' , 11.3 , 'CR' , 111.12 ) ,( 'DOT' , 100 , 'H' , 186.89 ) ----- testing selling >>> negative value Insert into PolcaDot ( Symbol , Quantity ,Trans_type ,Current_USD) values ('DOT' , -12 , 'B' , 100 ) Insert into PolcaDot ( Symbol , Quantity ,Trans_type ,Current_USD ,Transaction_Hash) -- testing with Hash values ('DOT' , 10 , 'b' , 30, '0xfeb138c320f01d255e3036c0ec42ce3023aa14bea11fe9218341d3667e4ee18A' ) -- success when inserting once , second failing as expected because Unique Nonclustered Index on column hash need to be unique Insert into PolcaDot ( Symbol , Quantity ,Trans_type ,Current_USD ) -- testing with Hash values ('GRT' , 10 , 'b' , 30 ) -- failed as expected because >>> CHECK constraint "Chk_Symbol_UP". The conflict occurred in database "Project_Crypto_tracker", table "dbo.PolcaDot", column 'Symbol' ############################### TESTING Stored Procedure ############################################################## Execute Crypto_INSERT 'Dot' ,19 ,6.93 -- transaction type NULL success ! Execute Crypto_INSERT 'Dot' ,20 ,5.52,'C' -- testing procedure with mix of letters in symbol column Execute Crypto_INSERT 'DOT' ,-9 ,6.78,'H' -- selling (negative value) Execute Crypto_INSERT 'LINK' ,1990 ,6.60,'cr','0xfeb138c320f01d255e3036c0ec42ce3023aa14bea11fe9218341d3667e4ee18L' Execute Crypto_INSERT 'CRO' ,50000 ,0.071334 Execute Crypto_INSERT 'XRP' , 10 ,55 -- not existing coin >>>> launches "if not" statement >> correct ! EXEC Crypto_INSERT 'CRO',303,0.071105,'CR' ########################################## The test passed successfully ########################################################
Creating View
GO CREATE VIEW Crypto_View_coins AS SELECT * FROM PolcaDot UNION SELECT * FROM Chainlink UNION SELECT * FROM [dbo].[Verge] UNION SELECT * FROM [dbo].[BitcoinCash] UNION SELECT * FROM [dbo].[Cronos] UNION SELECT * FROM [dbo].[Fantom] UNION SELECT * FROM [dbo].[JasmyCoin] UNION SELECT * FROM [dbo].[Polygon] UNION SELECT * FROM [dbo].[Reef] UNION SELECT * FROM [dbo].[TheGraph] ORDER BY Id_Transaction OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY
In this example, it is easy to explain why I used the SEQUENCE “newSeq”. Regardless of which table the coin was inserted, in this view all coins are sorted by date in ascending order thanks to the single sequence used on all tables instead of repeating Id_Transaction
Creating UDF on View
GO CREATE FUNCTION QUickView ( @Symbol char(10) ) RETURNS TABLE AS RETURN ( SELECT * FROM Crypto_View_coins WHERE @Symbol = Symbol) SELECT * FROM QUickView ('XVG') -- testing !!
Creating target price – table with desired price, selling price
SELECT Name ,Symbol ,SUM(Sum_tran) as [Total_USD] ,SUM(Quantity) as [Total_coins] ,[AverageBuyingPrice] = SUM(Sum_tran) / SUM(Quantity) ,[My_target] = '--->>>>' INTO #Target_Price_sell FROM [dbo].[Crypto_View_coins] GROUP BY Name ,Symbol ALTER TABLE #Target_Price_sell ADD [Trg_price_coin] DECIMAL (10,2) -- insert your desire target coin price update #Target_Price_sell SET Trg_price_coin = 400 WHERE Symbol = 'BCH' update #Target_Price_sell SET Trg_price_coin = 1.50 WHERE Symbol = 'CRO' update #Target_Price_sell SET Trg_price_coin = 45 WHERE Symbol = 'DOT' update #Target_Price_sell SET Trg_price_coin = 2 WHERE Symbol = 'FTM' update #Target_Price_sell SET Trg_price_coin = 1.50 WHERE Symbol = 'GRT' update #Target_Price_sell SET Trg_price_coin = 0.15 WHERE Symbol = 'JASMY' update #Target_Price_sell SET Trg_price_coin = 45 WHERE Symbol = 'Link' update #Target_Price_sell SET Trg_price_coin = 3.50 WHERE Symbol = 'MATIC' update #Target_Price_sell SET Trg_price_coin = 0.03 WHERE Symbol = 'REEF' update #Target_Price_sell SET Trg_price_coin = 0.05 WHERE Symbol = 'XVG' SELECT * ,FORMAT (ceiling (Trg_price_coin / AverageBuyingPrice * 100.0) ,'P0','en-GB' )as [PercentFromBuingPrice] ,FORMAT (Trg_price_coin * Total_coins ,'C','en-US' ) as [Total_Desired_Profit] INTO Personal_target FROM #Target_Price_sell SELECT * FROM Personal_target
Creating View summary – how much money was invested per coin
GO CREATE VIEW Crypto_summary as SELECT Name ,CAST( SUM(Quantity) AS INT ) as [Total_coins] ,FORMAT (SUM(Sum_tran),'C','en-US' )AS [USD_invested] FROM [dbo].[Crypto_View_coins] GROUP BY Name ORDER BY USD_invested desc OFFSET 0 ROWS FETCH NEXT 500 ROWS ONLY SELECT * FROM Crypto_summary -- testing
Now a few queries to introduce data investment
-- OVER SELECT -- Running_investment Name ,Symbol ,Date_time ,Quantity ,Trans_type ,Sum_tran ,SUM(Sum_tran) OVER ( PARTITION BY [Name] ORDER BY Sum_tran) as [Running_investment] FROM [dbo].[Crypto_View_coins]-- ROLLUP SELECT -- Summary every Month / Year / Total Name ,SUM(Sum_tran) AS [USD_invested] ,YEAR(Date_time) as [YYdate] ,MONTH(Date_time) as[MMdate] , CASE WHEN YEAR(Date_time) IS NOT NULL AND MONTH(Date_time) IS NOT NULL THEN 'Month_Summary' WHEN YEAR(Date_time) IS NOT NULL AND MONTH(Date_time) IS NULL THEN 'Year_Summary' WHEN YEAR(Date_time) IS NULL AND MONTH(Date_time) IS NULL THEN 'Coin_Summary' END AS [Info] FROM [dbo].[Crypto_View_coins] GROUP BY ROLLUP ( Name ,YEAR(Date_time) ,MONTH(Date_time) )
-- PIVOT GO WITH CTE_PIVOT AS ( SELECT Sum_tran ,YEAR(Date_time) as [YYdate] ,MONTH(Date_time) as[MMdate] FROM [dbo].[Crypto_View_coins] ) SELECT YYdate ,ISNULL ([1] ,0.00) AS [January] ,ISNULL ([2] ,0.00) AS [February] ,ISNULL ([3] ,0.00) AS [March] ,ISNULL ([4] ,0.00) AS [April] ,ISNULL ([5] ,0.00) AS [May] ,ISNULL ([6] ,0.00) AS [June] ,ISNULL ([7] ,0.00) AS [July] ,ISNULL ([8] ,0.00) AS [August] ,ISNULL ([9] ,0.00) AS [September] ,ISNULL ([10],0.00) AS [October] ,ISNULL ([11],0.00) AS [November] ,ISNULL ([12],0.00) AS [December] FROM CTE_PIVOT PIVOT ( SUM (Sum_tran) FOR MMdate IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT ORDER BY [YYdate]
