The Challenges of Writing Business Apps/Tools

As mentioned in my previous posts on this blog, my developer journey began as an intersection between the corporate world (accounting) and programming. I used to support my own accounting processes and help others with various VBA/Excel, Access, SQL tools and that's how the journey began.

Outgrowing my role as a Management Accountant in this technological sense, in 2019 I began studying and writing full time additional applications for business/accounting problems.

The basic idea of the tools is automating Excel-based processes in a more coherent database-app approach. There are a lot of struggles in my journey, but and I have learnt a lot, especially .Net technologies and SQL, and in the meantime, the web-stack using JavaScript and lately ASP.NET Core/Razor pages.

Clearly, the biggest challenge in all these is the mapping of real world processes (based on Excel and manual work) to an application. What's needed for this to be successful? In no particular order, my conclusions are:
1. very good understanding of the processes. This takes time and lots of communication/questions.
2. Understanding inputs and outputs. Data structure, types, formats, steps, validation, internal checks of the applications, stages of completion, reports, application logic and flow all come from inputs and outputs.
3. Thinking about the entity/DB structure and program flow.
4. From the mental mapping above come the DB structure, and then flow. Usually the flow is the succession of operations: input data, review data, edit, delete, search, etc, add more data, filter, prepare intermediary reports and then further add data, filter, edit if needed and then generate final outcome (reports, files). This cycle above can have multiple stages and checks/reports built within.
5. Validate the proposed process with the customer.
4. Discussing old methods vs new optimized process: lessons learned and areas to improve on in the new system based on feedback.

My big projects in the last 2 years are:
1.  Manufacturing Reporting/Statistics tool in MS ACCESS, on key phases of the production line. (manufacturing shop floor).
2.  Internal recharge system of the company, for distribution of indirect/administrative costs. Technology: C#, .Net, WPF/MVVM and SQL Server
3. My current project is an invoicing software that collects and invoices costs by employees. Using same technology as above. + Asp.net Core Razor pages
4. Another project for 2020 will be a VAT reporting tool to automate an Excel-based approach inside the accounting department, consolidating, filtering, automating data collection from several internal business units (big departments), and preparing statutory reports for Value Added Tax.

Some clear lessons to change the Excel-Based approach into a more efficient application are:
1. Using one database instead of many files, on a central server/Azure, for each app.
2. Mapping the entities from the excel files => as database tables/ and POCO entities.
3. All the automation of the repetitive processes go to C# code = > helper, data manipulation functions, using LINQ with lists etc.
4. Report generation, using ReportViewer tool, or ClosedXML to export excel files upon request;
5. Code re-usability.

Some architectural conclusions of the journey are:
1. need to manage the users of the application
2. implementing a reporting system, with parametrised queries, using SQL/Dapper/Reportviewer and/or simple Excel reports
3. implementing a concept of "Period": the accounting month in which all transactions take place. Afterwards the period is closed, and next month is opened for another series of transactions.
4. Implementing different types of Tables / entities by functionality:

• tables with reduced number of updates such as business units, customers, users
• tables with some  more frequent updates (such as employees, price lists)
• transaction tables (allocation keys calculated, monthly cost information, monthly invoice details, etc)

5. For transaction tables, insert the user and transaction date/time 

6. In some special cases, implement a history table for some entities for which the change log is important  (such as employee change history).

What do you think, what are the main points of producing such business/accounting tools? Would you do something differently from an architecture standpoint?