Today we look at how you can use ChatGPT and dbFront to rapidly create or extend custom database applications. In this case we are going to add a module to handle asset depreciation.
Asset depreciation refers to the decline in value of an asset over time due to usage, wear and tear, and obsolescence. Tracking asset depreciation is essential to ensure accurate financial reporting, to meet taxation requirements, and to plan for asset replacements.
Generating the Database Structure
ChatGPT describes itself as a "powerful language model that can generate natural language responses to any given prompt." Today we are going to examine ChatGPT's functionality as we attempt to generate the required database structure for an asset depreciation module. We are going to start with a simple request.
- Browse to chat.openai.com, and either login or create an account,
- Start a new chat and ask "Create a database to track asset depreciation",
- In response ChatGPT quickly generates a response that looks similar to the following.
Sure! I can help you create a simple database to track asset depreciation. Here's an example schema that you can use as a starting point:
Asset Table
| Column Name |
Data Type |
Description |
| asset_id |
integer |
unique identifier for the asset |
| asset_name |
varchar(50) |
name of the asset |
| purchase_date |
date |
date the asset was purchased |
| purchase_price |
decimal(10, 2) |
purchase price of the asset |
| useful_life |
integer |
expected useful life of the asset (in years) |
| salvage_value |
decimal(10, 2) |
estimated salvage value of the asset |
| depreciation_method |
varchar(20) |
method used to calculate depreciation (e.g. straight line, double declining balance, etc.) |
Depreciation Table
| Column Name |
Data Type |
Description |
| depreciation_id |
integer |
unique identifier for the depreciation entry |
| asset_id |
integer |
identifier for the asset that was depreciated |
| depreciation_date |
date |
date that the asset was depreciated |
| depreciation_amount |
decimal(10, 2) |
amount that the asset was depreciated for this period |
| book_value |
decimal(10, 2) |
book value of the asset after depreciation |
With this schema, you can record the details of each asset, including its purchase date, purchase price, expected useful life, and depreciation method. You can then create depreciation entries for each asset at regular intervals (e.g. monthly, quarterly, or yearly), recording the amount of depreciation for that period and the book value of the asset after depreciation. You can use this information to calculate the current value of your assets and plan for future purchases or upgrades.
What is interesting is that the structure includes a short explaination describing how this structure can be used. I noticed that it did not include a supporting table for the "depreciation_method" so I updated my request as follows.
Create a database to track asset depreciation and include the depreciationmethod
I then received an updated structure that included the missing table.
Once I was satisfied with the data structure I then made the following request to get the TSQL and some sample data.
Generate the TSQL create statements including the relationships and 10 rows of sample data in each table
ChatGPT responded with the TSQL code as follows. It paused during the code generation but I just told it to continue and it completed with only one minor hiccup in the sample data.
-- Creating the DepreciationMethods table
CREATE TABLE DepreciationMethods (
DepreciationMethodID INT PRIMARY KEY,
DepreciationMethodName VARCHAR(50)
);
-- Creating the Assets table
CREATE TABLE Assets (
AssetID INT PRIMARY KEY,
AssetName VARCHAR(50),
PurchaseDate DATE,
PurchaseCost DECIMAL(18,2),
SalvageValue DECIMAL(18,2),
UsefulLife INT,
DepreciationMethodID INT,
FOREIGN KEY (DepreciationMethodID) REFERENCES DepreciationMethods(DepreciationMethodID)
);
-- Creating the Depreciation table
CREATE TABLE Depreciation (
DepreciationID INT PRIMARY KEY,
AssetID INT,
[Year] INT,
[Month] INT,
DepreciationAmount DECIMAL(18,2),
AccumulatedDepreciation DECIMAL(18,2),
FOREIGN KEY (AssetID) REFERENCES Assets(AssetID)
);
-- Inserting sample data into the DepreciationMethods table
INSERT INTO DepreciationMethods (DepreciationMethodID, DepreciationMethodName)
VALUES
(1, 'Straight Line'),
(2, 'Double Declining Balance'),
(3, 'Sum of Years Digits');
-- Inserting sample data into the Assets table
INSERT INTO Assets (AssetID, AssetName, PurchaseDate, PurchaseCost, SalvageValue, UsefulLife, DepreciationMethodID)
VALUES
(1, 'Computer', '2022-01-01', 1000.00, 100.00, 5, 1),
(2, 'Printer', '2022-02-01', 500.00, 50.00, 3, 2),
(3, 'Forklift', '2021-07-01', 5000.00, 1000.00, 10, 1),
(4, 'Office Chair', '2022-03-15', 100.00, 10.00, 7, 3),
(5, 'Delivery Van', '2021-01-01', 20000.00, 2000.00, 5, 2),
(6, 'Construction Equipment', '2020-06-01', 50000.00, 5000.00, 8, 1),
(7, 'Copier', '2022-02-15', 1500.00, 150.00, 4, 2),
(8, 'Office Desk', '2021-11-01', 200.00, 20.00, 10, 3),
(9, 'Delivery Truck', '2020-05-01', 50000.00, 5000.00, 7, 1),
(10, 'Conference Table', '2022-01-15', 800.00, 80.00, 5, 2);
-- Inserting sample data into the Depreciation table
INSERT INTO Depreciation (DepreciationID, AssetID, [Year], [Month], DepreciationAmount, AccumulatedDepreciation)
VALUES
(1, 1, 2022, 1, 166.00, 166.00),
(2, 1, 2022, 2, 166.00, 332.00),
(4, 1, 2022, 4, 166.00, 498.00),
(5, 1, 2022, 5, 166.00, 664.00),
(6, 2, 2022, 1, 50.00, 50.00),
(7, 2, 2022, 2, 66.67, 116.67),
(8, 2, 2022, 3, 83.33, 200.00),
(9, 2, 2022, 4, 50.00, 250.00),
(10, 2, 2022, 5, 33.33, 283.33),
(11, 3, 2021, 7, 416.67, 416.67),
(12, 3, 2022, 1, 416.67, 833.34),
(13, 3, 2022, 7, 416.67, 5000.00),
(14, 4, 2022, 3, 9.52, 9.52),
(15, 4, 2022, 4, 13.61, 23.13),
(16, 4, 2022, 5, 11.00, 34.13),
(17, 5, 2022, 1, 8000.00, 8000.00),
(18, 6, 2022, 1, 6250.00, 6250.00),
(19, 6, 2022, 6, 6250.00, 31250.00),
(20, 7, 2022, 2, 300.00, 300.00),
(21, 7, 2022, 3, 360.00, 660.00),
(22, 7, 2022, 4, 216.00, 876.00),
(23, 8, 2021, 11, 4.17, 4.17),
(24, 8, 2022, 1, 4.17, 8.33),
(25, 8, 2022, 11, 4.17, 45.83),
(26, 9, 2020, 5, 5208.33, 5208.33),
(27, 9, 2021, 1, 5208.33, 10416.67),
(28, 9, 2021, 5, 5208.33, 31250.00),
(29, 10, 2022, 1, 160.00, 160.00),
(30, 10, 2022, 2, 160.00, 320.00);
One point that could be made is that the Asset table is thin on any details not related to depreciation. In this case that is useful because the intention would be to connect this depreciation functionality to an exist asset management database via a 1 - 1 join. Not all assets require depreciation.
I went further asking ChatGPT to generate the required stored procedure which would calculate the yearly depreciation.
Generate the Procedure required to calculate the depreciation
The result was an incomplete procedure and requesting it to continue, did not result in a useful function.
I then sent three separate requests as follows, one for each depreciation type.
Generate the Procedure required to calculate the depreciation for "Straight Line"
Generate the Procedure required to calculate the depreciation for "sum of years digits"
Generate the Procedure required to calculate the depreciation for "Double-Declining-Balance"
Followed by a request to create a procedure that would call the correct procedure based on the depreciation type.
Generate a procedure called CalculateDepreciation, that will call CalculateSLDepreciation or CalculateDDBDepreciation or CalculateSYDDepreciation based on the Depreciation type of the asset
This resulted in four separate procedures as follows.
/**************************************************************************/
CREATE PROCEDURE CalculateSLDepreciation
@AssetID INT,
@Year INT
AS
BEGIN
DECLARE @UsefulLife INT
DECLARE @AcquisitionCost MONEY
DECLARE @SalvageValue MONEY
DECLARE @DepreciationRate FLOAT
DECLARE @AccumulatedDepreciation MONEY = 0
DECLARE @DepreciationAmount MONEY
SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
FROM Asset
WHERE AssetID = @AssetID
IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
BEGIN
RAISERROR('Asset not found or incomplete data', 16, 1)
RETURN
END
SET @DepreciationRate = (@AcquisitionCost - @SalvageValue) / @UsefulLife
IF @DepreciationRate IS NULL
BEGIN
RAISERROR('Invalid useful life', 16, 1)
RETURN
END
WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
BEGIN
SET @DepreciationAmount = @DepreciationRate
IF @DepreciationAmount > (@AcquisitionCost - @AccumulatedDepreciation)
SET @DepreciationAmount = @AcquisitionCost - @AccumulatedDepreciation
IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation
INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)
IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + 1 > 12
BREAK
SET @Year = @Year + 1
IF @DepreciationAmount < @SalvageValue
BREAK
SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
END
END
/**************************************************************************/
CREATE PROCEDURE CalculateDDBDepreciation
@AssetID INT,
@Year INT
AS
BEGIN
DECLARE @UsefulLife INT
DECLARE @AcquisitionCost MONEY
DECLARE @SalvageValue MONEY
DECLARE @DepreciationRate FLOAT
DECLARE @AccumulatedDepreciation MONEY = 0
DECLARE @DepreciationAmount MONEY
SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
FROM Asset
WHERE AssetID = @AssetID
IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
BEGIN
RAISERROR('Asset not found or incomplete data', 16, 1)
RETURN
END
SET @DepreciationRate = 2.0 / @UsefulLife
IF @DepreciationRate IS NULL
BEGIN
RAISERROR('Invalid useful life', 16, 1)
RETURN
END
WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
BEGIN
SET @DepreciationAmount = (@AcquisitionCost - @AccumulatedDepreciation) * @DepreciationRate
IF @DepreciationAmount > (@AcquisitionCost - @AccumulatedDepreciation)
SET @DepreciationAmount = @AcquisitionCost - @AccumulatedDepreciation
IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation
INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)
IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + 1 > 12
BREAK
SET @Year = @Year + 1
IF @DepreciationAmount < @SalvageValue
BREAK
SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
END
END
/**************************************************************************/
CREATE PROCEDURE CalculateSYDDepreciation
@AssetID INT,
@Year INT
AS
BEGIN
DECLARE @UsefulLife INT
DECLARE @AcquisitionCost MONEY
DECLARE @SalvageValue MONEY
DECLARE @DepreciationRate FLOAT
DECLARE @AccumulatedDepreciation MONEY = 0
DECLARE @DepreciationAmount MONEY
SELECT @UsefulLife = UsefulLife, @AcquisitionCost = AcquisitionCost, @SalvageValue = SalvageValue
FROM Asset
WHERE AssetID = @AssetID
IF @UsefulLife IS NULL OR @AcquisitionCost IS NULL OR @SalvageValue IS NULL
BEGIN
RAISERROR('Asset not found or incomplete data', 16, 1)
RETURN
END
SET @DepreciationRate = 1 / ((@UsefulLife * (@UsefulLife + 1)) / 2)
IF @DepreciationRate IS NULL
BEGIN
RAISERROR('Invalid useful life', 16, 1)
RETURN
END
WHILE MONTH(DATEFROMPARTS(@Year, 1, 1)) <= 12
BEGIN
SET @DepreciationAmount = (@AcquisitionCost - @AccumulatedDepreciation) * @DepreciationRate *
(DATEDIFF(month, DATEFROMPARTS(@Year, 1, 1), DATEADD(year, 1, DATEFROMPARTS(@Year, 1, 1))) + 1) / 12
IF MONTH(DATEADD(month, DATEDIFF(month, -1, DATEFROMPARTS(@Year, 1, 1)), -1)) = 12
BEGIN
SET @DepreciationAmount = @DepreciationAmount - (@AcquisitionCost - @AccumulatedDepreciation - @DepreciationAmount) * @DepreciationRate
END
IF @DepreciationAmount < @SalvageValue - @AccumulatedDepreciation
SET @DepreciationAmount = @SalvageValue - @AccumulatedDepreciation
INSERT INTO Depreciation (AssetID, DepreciationDate, DepreciationAmount, AccumulatedDepreciation)
VALUES (@AssetID, DATEFROMPARTS(@Year, MONTH(DATEFROMPARTS(@Year, 1, 1)), 1), @DepreciationAmount, @AccumulatedDepreciation)
IF MONTH(DATEFROMPARTS(@Year, 1, 1)) + DATEDIFF(month, DATEFROMPARTS(@Year, 1, 1), DATEADD(year, 1, DATEFROMPARTS(@Year, 1, 1))) > 12
BREAK
SET @Year = @Year + 1
IF @DepreciationAmount < @SalvageValue
BREAK
SET @AccumulatedDepreciation = @AccumulatedDepreciation + @DepreciationAmount
END
END
/**************************************************************************/
CREATE PROCEDURE CalculateDepreciation
@AssetID INT,
@Year INT
AS
BEGIN
DECLARE @DepreciationMethod VARCHAR(50)
SELECT @DepreciationMethod = DepreciationMethod
FROM Asset
WHERE AssetID = @AssetID
IF @DepreciationMethod = 'Straight-Line'
BEGIN
EXEC CalculateSLDepreciation @AssetID, @Year
END
ELSE IF @DepreciationMethod = 'Double-Declining-Balance'
BEGIN
EXEC CalculateDDBDepreciation @AssetID, @Year
END
ELSE IF @DepreciationMethod = 'Sum-of-Years-Digits'
BEGIN
EXEC CalculateSYDDepreciation @AssetID, @Year
END
ELSE
BEGIN
RAISERROR('Invalid depreciation method', 16, 1)
RETURN
END
END
Testing the Results
The next step was to test the results and create the structure and the procedures to verify that they worked as a whole.
Observations on the Structure
The data structure appeared to hold together quite well and only required minor correction. The sample data was able to load into the structures and the lookup values were relevant. The depreciation values were incorrect but sufficient as far as sample data was concerned.
Depending on how I asked, ChatGPT could generate a variety of different structures.
- Sometimes a column for the SalvageValue was present, sometimes not,
- The DepreciationMethod could be a missing, a simple attribute, or a foreign key with lookup table,
- The date of the depreciation could either be stored in a Date column named EntryDate, or else it might be broken up in to Year and Month columns, or ChatGPT might generate a Period table.
- When I asked ChatGPT to name all tables in the singular it create an entirely new structure.
It was helpful to have ChatGPT return different structures since each structure showed specific benefits. I found that there was too much variety in the ChatGPT results for them to be brushed of as simple canned responses.
Observations on the Code
The stored procedures were very disappointing. The code looked plausible and showed just enough undertanding to awaken hope. Upon deeper examination it was clear that the code contained massive logic errors, invalid object names and references and/or could not compile. The procedures were nowhere near ready to run.
Observations on the Source
When I asked ChatGPT for its sources, it responded as follows.
This answer was generated solely by me, ChatGPT, a language model created by OpenAI. The database structure and sample data were based on my own understanding of asset depreciation, and the stored procedure was written by me using T-SQL syntax.
When I asked ChatGPT for "specific references for your understanding", it responded as follows.
As an artificial intelligence language model, I don't have subjective opinions or personal experiences. However, the understanding of asset depreciation used in my answer is based on general accounting principles and industry best practices. Some relevant sources for information on this topic include:
- The Generally Accepted Accounting Principles (GAAP) and International Financial Reporting Standards (IFRS) for accounting treatment of asset depreciation.
- The U.S. Internal Revenue Service (IRS) tax code and publications for tax-related depreciation rules.
- Accounting textbooks and online courses for comprehensive explanations and examples of asset depreciation methods and calculations.
- Industry-specific guides and regulations for asset depreciation in particular sectors, such as real estate, manufacturing, or mining.
I used this general understanding of asset depreciation to create a basic relational database structure and stored procedure that could be used to manage depreciation in a simple system.
I also followed up by running various searches for sample code or data structures for Asset Depreciation and found very little that came anywhere close to the results that ChatGPT returned.
I will leave it to others to figure out how ChatGTP actually arrived at its understanding of depreciation and what that means for us mere mortals.
Adding the UI & bringing dbFront into the mix
The next logical step was to have ChatGPT generate a UI for the data structure it generated.
ChatGPT generated UI
When I asked ChatGPT to create a single page UI for the data structure it responded as follows.
As an AI language model, I cannot generate a user interface (UI) for you, but I can provide you with a general idea of what a single-page UI for this data structure might look like.
It then proceeded to list out what it felt were the specific requirements for the UI. The rest of the reponse follows.
Assuming you want a UI that allows you to view, add, edit, and delete assets and their depreciation information, here are some possible components:
-
A navigation bar at the top of the page that includes links to different sections of the app (e.g. "Dashboard", "Assets", "Depreciation Methods", "Reports").
-
A dashboard section that provides an overview of your assets and their current depreciation status (e.g. a summary of assets by category, a chart of depreciation expenses over time, etc.).
-
An "Assets" section that allows you to view and manage your assets. This section could include:
-
A table that lists all of your assets, with columns for asset name, purchase date, cost, salvage value, useful life, and current depreciation status.
-
Buttons or icons that allow you to add a new asset, edit an existing asset, or delete an asset.
-
A modal window or form that appears when you click on an asset in the table, allowing you to view and edit its details.
- A "Depreciation Methods" section that allows you to view and manage the different depreciation methods available in your system. This section could include:
-
A table that lists all of the depreciation methods, with columns for method name, description, and formula.
-
Buttons or icons that allow you to add a new method, edit an existing method, or delete a method.
-
A modal window or form that appears when you click on a method in the table, allowing you to view and edit its details.
- A "Reports" section that allows you to generate reports on your assets and depreciation expenses. This section could include:
-
A dropdown menu that allows you to select a report type (e.g. "Asset List", "Depreciation Schedule", "Expense Report").
-
A form that allows you to specify parameters for the report (e.g. date range, asset category, etc.).
-
A preview pane that displays a preview of the report, with options to export it as a PDF or Excel file.
- A footer section that includes links to your company's social media pages, contact information, and legal notices.
Of course, the exact design and functionality of the UI will depend on your specific needs and preferences. But this should give you a general idea of what a single-page UI for this data structure might look like.
Not willing to give up so quickly, I asked it to "Generate a web application to maintain those tables.". ChatGPT then proceeded to spit out a basic PHP web application. A fair bit of prompting was required before it would spit out the entire application. With more direction and prompting, ChatGPT moved to jQuery, AJAX and Datatables.net to generate a more advanced application.
Some things that stood out were:
- The generated UI is heavily tied to the data structure so any data structure changes would result in significant refactoring.
- The UI was simplistic and additional functionality would require a significant amount of work and experience.
- ChatGPT left out pieces, possibly expecting you to specifically ask for the missing items.
- The generation limits force you to request specific pieces of the UI or database structure and then stitch them together.
- Because of these issues and the previous failures with the stored procedures I abandoned the generated UI.
Using dbFront for the UI
I then opened dbFront and pointing it to the database that contained the newly created asset depreciation structures.
In short order I had a UI that satisfied 5 out of the 6 UI requirements that ChatGPT had outlined.
- A navigation menu showing the core tables,
- A home screen with instructions.
- An assets view that allowed you to fully manage all aspects of the asset. In addition you had the option of calculating next years depreciation.
- A maintenance screen for the depreciation methods,
- Reports were available on the home screen and related asset.
- A fully configurable footer.
The only missing item was a dashboard containing a summary of the data with a chart. The asset summary and chart could be created using a Crystal Report or export to an Excel template containing a chart.
Because dbFront generates it's UI dynamically, it automatically follows along as the database structure changes. This also applies to any of its template based reports or exports. This is specifically helpful because it allows a DBA to continue working on the database structure without having to recreate the UI.
Demo
We took the Depreciation Calculator database structure and the dbFront config and migrated it to our demo environment so that you can see it in action.
Below is a link to the Demo environment and also a link where you can download the source. For the demo environment, you can use the usernames dbAdmin or dbGuest and the password "password".
Wrap Up
There is a significant amount of hype around ChatGPT and some of that hype is deserved, but not all. What ChatGPT generates looks impressive but it fails to show real comprehension of the actual material and therefore it can't self correct. It simply regurgitates plausible looking answers. ChatGPT's inability to self correct is most obvious when it generates reasonable looking code that is incomplete, incorrect, or won't compile. Correcting code salad (think "word salad") is not trivial and often requires a rewrite.
ChatGPT appears to have a good ability to assemble what belongs together and even some concept of sequence. But it just can't do the logic to wire things together correctly and validate that what it is doing is correct.
What we can salvage from this experiment is that ChatGPT showed an interesting ability to assemble a reasonable looking data structure for a known business problem. Any errors in the structure were easy to fix and the ability to generate sample data was useful. The caveat is that ChatGPT requires direction by someone who understands the business requirements and the implications of any database structure decisions.
dbFront again proved to be a dynamic and solid choice when it came time to build the UI.
To check out dbFront, go to dbFront.com.
In the next blog post we are going to try to solve a more complex business problem with ChatGPT & dbFront. Till then.
Continue Reading...
