Assessing Project Size (1 Viewer)

[CraigBaker]

Who needs Google? Searching through this site has taught me so such, and I'm still reading. Thankyou everyone!

I have a high level question, and apologies if this is not appropriate. I also understand there will be no definitive answer, but a conversation could be very enlightening, hopefully for others as well.

I've inherited an Access application in a company that is not particularly IT savvy. One of their managers did some restructuring of the company and developed an application to help run the business. The system allows staff to enter timesheets, purchase orders, invoices, etc, and then allows project managers and executives to run reports to understand the health of various projects, and the company as a whole. Pay information is exported to MYOB, so the staff are not paid from within the application, but the breakdown of pays (allowances, overtime, double time, etc) is calculated and stored in the system, before exporting to MYOB.

The system also manages Leave applications, and training/staff certification records. The system was installed in early 2023, and the manager left the company around April 2023, having undertaken virtually no testing. I started at the company in September 2023 on a temporary basis to fix the most obvious problems. The temporary basis is continuing, but one of the owners of the company has shown an interest for the last couple of weeks, so the role of the system is becoming slightly clearer.

This is my long-winded way of saying this is intended to be a pretty comprehensive system.

My question is in two parts:

1. How big is this project? How many developers are required? How much money should the company plan to spend developing this in-house?
2. What are the important metrics to help answer 1? Number of tables, number of employees, ...?

The foundations of the project are very good, but most of it didn't (doesn't) work, and it isn't optimised for speed. As people use it, they have ideas to change and improve it.

My attempt to define the project (to answer question 2), which I hope can be used to answer question 1, is as follows:

• The company has approximately 150 staff. The majority of staff are plumbers and other trades, with support staff such as project managers, accounts staff, etc. There are large numbers of sub-contractors and apprentices with numbers fluctuating depending on current project load.
• The company has 257 projects in the system, with approximately 100 active, and another 40 in the "maintenance" phase.
• The system has approximately 80 tables in the SQL Server backend (with none in the Access front end).
• The system has approximately 96 views in SQL Server.
• Tables and views in Access are links to the backend.
• SQL Server is installed on an in-house server.
• The system has 18 stored procedures, and 0 triggers.
• There are 2459 lines of code in 15 stand alone modules.
  • 22,250 lines of code in 145 modules behind forms.
  • 350 lines of code in 28 reports.
• Primary keys were created in all tables, and some default constraints (many of which caused problems), but virtually no foreign keys and other indexes (indices).
• There is also a website that uses the SQL Server database, which currently just handles timesheets and purchase orders, but there is code to do many other things. I have hidden the buttons for most of the functionality on the website, because I've needed to re-write about 90% of the site so far, and I expect it will be the same for the rest of it, if we decide to use it. At this stage, I'm not including this in 1, because I might just use 10% of my time maintaining this, now that it is working at a very basic level.
• I received no documentation about the system. (Thanks Doc, for reminding me of this).

My gut feeling answer to 1 is that the project probably needs 2-3 developers, for at least a couple of years, and possibly forever, if it ends up being used as designed. The cost is just the salary of the developers, with some of that being used for a business analyst or liason between the developers and the business. The server, etc, is already paid for and maintained by an external IT company.

Am I way off here? I feel the need to warn them that it may be cheaper to buy something off the shelf, even though that option would have its own problems. That would cost me a job, but I'd rather everyone goes into this with their eyes open.

Do I need to provide more information to answer 2? Or less information?

Thanks in advance. I'd better get back to it!

Craig

 

[plog]

Not an answer to your questions, but my initial overall thought:

Ditch this and hire a company to do it for you.

And by "this" I don't mean "the development of the database", I mean to give you a time management solution. There are a lot of companies that provide this software/service, I suggest you contact a few and get bids. Your organization isn't that special that it needs to invest and constantly maintain its own software for this, nor is it so small that they can get away with an Access based home grown solution. It's time to move this off your plate and have people who have solved this problem handle it.

 

[The_Doc_Man]

You might have to search for this in a university library, but look for works by Barry Boehm on project estimating or project management. I seriously doubt his books are still in print but a decent library might have something under a management topic. He wrote other things on economics so be sure that you look for his project management works.

There is also a book called "The Mythical Man-Month" that is a must-read reference for anyone planning a project. It points out the fallacies of throwing people at a project without a clear plan on how to spend their time productively. For instance, there is the example of a typical managerial question asked by a manager wanting to speed up production of something. So they say "It takes a woman nine months to make a baby, so how long should it take if we hire nine women?" Some things can't be rushed or put into a divided-labor situation.

Projects have choke points and divisible paths that can be managed in parallel. Without first identifying those features, you cannot make a project schedule chart, also known as a Gannt chart, to determine exactly how long a project will take at minimum. And to be clear, Gannt charts tell you the LEAST time it CAN take, not the actual time it WILL take. There is a certain "uncertainty" principle in effect, or perhaps it is like the Second Law of Thermodynamics - for any spontaneous process, entropy increases. In a project, "entropy" correlates with "chaos" which correlates with delays and difficulties.

Believe it or not, most of the personnel statistics you quoted have nothing to do with the complexity of the project. Though they might contribute to storage or processor requirements long-term, there is no particular difference in a master payroll system for 200 people or 500 people EXCEPT for the number of records involved - and that is a capacity issue, not a complexity issue.

The tables, queries, lines of code etc. ARE contributors to complexity, but the REAL question is whether your predecessor left you "the bible" to go with the infrastructure he left behind. By "the bible" I mean a project design document. If a proper project analysis occurred, a document should have been created to record the factors and decisions involved in laying out the structure of the application as a whole and in its parts. This document is a "project bible" that details inputs, outputs, algorithmic choices and why each one was chosen vs. alternatives, ... a blueprint for the project or perhaps a meta-data inventory of project elements. If you don't have that, you are - to be honest - screwed because you have NO idea which way anything was going and you have no idea whether your planned fix actually would do the job without skewering data flow in another program segment.

As to the number of people required in order to implement this putative project, ... that is going to depend on a LOT of issues, but the first question has to be like this: How long can the company continue to run like it is running now? (You said the manager who built this left in Apr/23 and you came on in Sep/23 - 5 months.) How much money is it losing (or failing to capture) by continued operation in the current posture?

It would be productive to try to get some quotes on business software and figure out how soon you can get it vs. how quickly you can develop something. That would directly feed a buy/build decision. And if you don't have the project bible as a guidebook, include the generation of said bible as part of the necessary cost. Because without one, you cannot hand off tasks to multiple people anyway. They will need to pick your brain and you'll never get any work done.

Access is not like some development environments because every person who needs to work on a front-end section needs a separate copy that won't be influenced by the work of others. But with a product like Access where the code is in a single FE file, the ability to make changes in the FE might interfere with another person ALSO working on the FE file.

 

[CraigBaker]

Not an answer to your questions, but my initial overall thought:

Ditch this and hire a company to do it for you.

And by "this" I don't mean "the development of the database", I mean to give you a time management solution. There are a lot of companies that provide this software/service, I suggest you contact a few and get bids. Your organization isn't that special that it needs to invest and constantly maintain its own software for this, nor is it so small that they can get away with an Access based home grown solution. It's time to move this off your plate and have people who have solved this problem handle it.

Thanks for the reply. You've raised a couple of points that I want to address.

But first, I probably didn't describe the situation well enough. I am in fact "the company to do it for them". I'm an experienced software developer who can develop this software, but it will take me some time. If they hire a company to do it for them, I'm out of a job. That may be the best solution for them, but this is not my company.

Also, I'm not sure what you mean by a "time management solution". If you mean my time management, then I'm a developer, so I want to spend my time developing software. If you're describing the system as a time management system, I think it is more than that. It is (planned to be) a system to run virtually the whole business.

Interesting that you say the organisation isn't special enough to build their own system. That is definitely one of my questions. They may be imagining having a "home grown solution", but I've been clear with them that what they are proposing is a fully engineered software solution.

One of my first issues was whether Access was the right tool. Maybe I got this impression from this site , but I think Access can be the right tool. My working theory is that it will be better to use the current code base and fix/extend it, rather than start again in some other language. Of course I could be wrong, and that is partly what this thread is about.

I hope this clarifies things.

 

[CraigBaker]

The tables, queries, lines of code etc. ARE contributors to complexity, but the REAL question is whether your predecessor left you "the bible" to go with the infrastructure he left behind. By "the bible" I mean a project design document. If a proper project analysis occurred, a document should have been created to record the factors and decisions involved in laying out the structure of the application as a whole and in its parts. This document is a "project bible" that details inputs, outputs, algorithmic choices and why each one was chosen vs. alternatives, ... a blueprint for the project or perhaps a meta-data inventory of project elements. If you don't have that, you are - to be honest - screwed because you have NO idea which way anything was going and you have no idea whether your planned fix actually would do the job without skewering data flow in another program segment.

Thanks Doc,

I pretty much agree with you 100%, but there is no way this company will pay for the type of planning you are describing. And I'm not going to do it for free. See the end of this message for a higher level discussion of your response.

A fantastic point about the project bible. No documentation, and I very much doubt that a project analysis occurred. I kept being told that my predecessor was still helping with the system, even after he moved overseas, got a new job, and his wife had a baby. All I could get from him was a couple of emails saying he would reply to my questions when he had time, and he didn't make any of the planned weekly Zoom meetings.

None of this is ideal, but we have made good progress so far, hence my interest in now looking a bit more into the future.

As to the number of people required in order to implement this putative project, ... that is going to depend on a LOT of issues, but the first question has to be like this: How long can the company continue to run like it is running now? (You said the manager who built this left in Apr/23 and you came on in Sep/23 - 5 months.) How much money is it losing (or failing to capture) by continued operation in the current posture?

The current situation is not costing them any money. They used spreadsheets, etc, before the system started to be developed, and they continue to use them where necessary. Really, the only cost is me! If they stopped paying me, they would save money, and many of the staff would be happy to go back to the old ways. I think they can do better, but at what cost?

It would be productive to try to get some quotes on business software and figure out how soon you can get it vs. how quickly you can develop something. That would directly feed a buy/build decision. And if you don't have the project bible as a guidebook, include the generation of said bible as part of the necessary cost. Because without one, you cannot hand off tasks to multiple people anyway. They will need to pick your brain and you'll never get any work done.

Again, I don't want to formally get buy/build quotes, because their requirements are too vague, and I don't think they'd want to pay for that expense anyway. And I have created a planned task for me to produce the project bible, but it hasn't seen much progress.

Access is not like some development environments because every person who needs to work on a front-end section needs a separate copy that won't be influenced by the work of others. But with a product like Access where the code is in a single FE file, the ability to make changes in the FE might interfere with another person ALSO working on the FE file.

Very good point, but I exported the code into text files and have them in Git, so I think I can manage the multi developer problems. I've had to do that before in other projects.

More General Discussion
I'm trying to balance coding and fixing bugs with some level of tracking and planning, so I can warn them about what they are getting into. It sounds like you agree with my gut estimation that this is likely to need multiple people for multiple years. I would like to tell them that sooner rather than later, in case they want to pull the pin entirely. But for various reasons, I'd like to stay in this job and not be required to find a different job.

I think the Mythical Man Month is a great book, and I may still have a copy somewhere. The name Barry Boehm sounds familiar too. I've done Project Management qualification and had experience much more recently than I read those books, but I think I still use the fundamentals I got at Uni at least as much as more recent knowledge.

Thanks for pointing out that I probably missed the most important metric for this project. I received not one piece of documentation for the system. Only a few people even used the system before I got here, so I'm having to teach myself how to use it as well.

I agree with what you are saying about the size of the company, but I think it does give some perspective on the project. As you say, I'm not particularly worried about the number of rows, etc in each table. It's more the complexity and understanding the system.

Craig

 

[ebs17]

I am in fact "the company to do it for them". I'm an experienced software developer who can develop this software

Database development is a special branch within programming because it involves processing a lot of data very quickly and certain structures for data storage are required for efficient work (SQL).

So how would you assess the existing skills in terms of data modeling and SQL. Because this is about the basis of everything.

If I have to look at a foreign database, my first look is at the database schema with all the relationships between the tables, because this provides the logic for everything that follows and provides an initial understanding.
This also raises the question of whether the existing database schema maps and can support the required workflow in the company. If a lot of things don't work yet, the reasons may already lie there. Corrections to this “foundation” are always less painful at the beginning than in later phases.

But these are “just” considerations on the backend.
Access will only play the role of the front end, i.e. the graphical user interface for the database. However, it can be used very successfully in this role, especially if you make sure that you let the powerful active database management system (SQL Server) work where possible and then only do supplementary and finalizing things with access codes.
So I don't count queries and lines of code, I would first look at processes.

 

[Minty]

We took over a well documented and well written, but in need of modernising system that sounds very similar to yours.
It was having issues with size performance, occasional corruption and wasn't split!

We decided to move it to Azure SQL server, as they had a new requirement for web based portal access.

The whole thing has taken approximately 18 months of on/off development, and we seriously underestimated the amount of changes required due the complexity of the system and the number of "moving parts" that affected each other.

If the current system isn't really working, and not being used, I would strongly suggest you re-write it from scratch, and avoid the pitfalls of inheriting stuff you didn't write.

 

[NauticalGent]

If the current system isn't really working, and not being used, I would strongly suggest you re-write it from scratch, and avoid the pitfalls of inheriting stuff you didn't write.

I am not refuting Minty's advice in the slightest - he wisely caveated this sentence with "If the current system isn't really working, and not being used..."

Neither one of those applied to my situation but I still thought that since I was superior to the previous "developer" (tongue in cheek), I could turn out a better product in no time and everything would be hunky-dory.

I was a fool to think this...

I spent crazy amounts of time backwards engineering the application and in most cases, ended up doing it the same way the "inferior" developer did. After about a year of rewriting and tweaking and cussing and pulling my hair out, I stumbled across this article. Even if I HAD come across it before hand, I probably would not have heeded it's advice. As my grandpa would say "You can tell some folks to come out of the rain, some fools gotta get wet..." I am a "get wet" kinda guy...

Point I am trying to make is this: As Minty said, if it is useless as it is, go scorched-earth, if it isn't, give it an objective evaluation and make sure you fully understand the scope of the product.

 

[The_Doc_Man]

My gut feeling answer to 1 is that the project probably needs 2-3 developers, for at least a couple of years, and possibly forever,

An addendum: Not 2 or 3 people forever, but at least 1 person for as long as it is in use. Part of the problem is that at some point you might face archiving that might not be there now. At some point, since you are dealing with personnel issues, the laws in your area regarding labor issues might change and thus require tinkering. At some point, the current version of Windows and/or Access and/or Excel may become unsupported, thus requiring upgrades and thus possible tweaking of what you have now to make it work the same in a new environment.

Having an in-house developer has its values but also has its risks... and that company just faced one. The original developer is gone. Now YOU have to step in. It is a business risk that at some point, they may have to get someone else if you get a new, much more lucrative, but absolutely immersive contract that gives you not outside time. (Happened to the company that employed my step-daughter when SHE got recruited for a computer security company.) For you as a contractor it is good to be wanted - but bad the moment conflicts of interest come into play.

 

[plog]

By "time management solution" I meant an HR package from an outside vendor. That seemed to be a big part of its functionality. You may need a few software packages (project management, HR, invoicing).

The size and importance of the system has outgrown a custom solution. Somewhere between 5 employees and 10,000 you need to move from homegrown solutions to enterprise ones. It will be painful no matter when you do that, but the smaller you are when you make that move the less pain, cost and disruption you will experience when you do.

If you do choose a homegrown solution and this will be the backbone of your business you will need at minimum 2 excellent full time people for ever or 4 mediocre ones for ever. Redundancy and availability are the main issues. You will forever need people who understand the whole thing inside and out and can drop everything and focus on fixing its problems if a huge one occurs. That of course is another reason for an enterpise solution--when things go wrong with an outside program an admin can be tasked with calling the vendor and hounding them until any issue gets addressed.

 

[Pat Hartman]

One of my first issues was whether Access was the right tool. Maybe I got this impression from this site , but I think Access can be the right tool. My working theory is that it will be better to use the current code base and fix/extend it, rather than start again in some other language. Of course I could be wrong, and that is partly what this thread is about.

I have several clients that run their entire business except for GL and Payroll using various Access applications.

There is NO CRM that solves all problems no matter what they tell you. Every CRM I have ever worked "did everything" but it did nothing well. I spent years developing mainframe applications to solve the problems that SAP and others like it said they solved but didn't. It's a little like my "smart phone". Too smart for me but not very good at being a phone.

If the client were to contact a company that specialized in web apps, that company would recommend recreating the apps to run in a browser for a cost of potentially millions depending on the actual complexity of the app. And the solution would almost certainly be unsatisfactory. If the client were to contact Salesforce, they would be sold a Salesforce solution and told that Access is a toy.

Access is an excellent (cheap, fast) tool for a certain class of applications.
1. The apps run on a LAN. If you need remote access for employees, then RD and Citrix are the best options to allow people to work remotely if necessary. If you need to support anonymous users as this web site does, Access is NOT your tool.
2. One developer at a time per app. Access is not suitable as a multi-developer tool unless the apps are isolated in their own FEs.

Once you upsize the BE to SQL Server, you eliminate three of the worst bad things about Access as a development platform. Your database size is now limited to what SQL Server can support which is far larger than the 2G limit for ACE. Your concurrent user limitations are also eliminated. You are now dependent on the number of SQL Server seat licenses. Your data is much more secure. At least no one is walking out of the office with the data on a thumb drive.

As the others have pointed out, the number of records is irrelevant provided the application has a sound design. I have apps where the largest tables run into the millions of records.

Sounds like you need to get control over the schema. Having NO RI is a poor design. Of course, adding it now will be a problem because there is almost certainly bad data in many of the tables. The bad data needs to be fixed or removed before RI can be established.

The app probably has little if any validation so you need to address that also.

The client has been struggling for a year. Get them to prioritize so you can concentrate on the most pressing bugs/features. Also, get them to commit to creating a testing team to approve all new releases so you can provide a better product. Developers are poor testers. We wrote the code so of course it will work. Users do some seriously stupid things but you need that perspective to lock down the app properly.

One thing I would suggest to help you to get a handle on a project of this size is to get the company to invest in Total Access Analyzer from www.fmsinc.com FMS provides numerous other useful tools plus lots of great articles.

If you want us to look at the schema, you can post it. If the naming standards are good and the app is one we will recognize, we can help without having the actual app and data also.

I still have my copy of the Mythical Man Month and I have had discussions with way too many managers who thought that time and people were interchangeable.

 

[CraigBaker]

Wow. Thanks for all the replies. Plenty of great advice. I can't reply to them all individually, so I'll put some general comments here.

I think everyone is confirming my thoughts that this is a significant project, requiring multiple developers. Please disagree if necessary.

First, some more clarifications. I have experience in SQL and databases, and Project Management. I don't have all the answers, but I know enough to navigate through most of the issues raised. In particular, recognising when I'm out of my depth, and looking for help.

Also, the DB schema is quite good. Well normalised, and models the world of the business well. Of course, not perfect, but probably the main strength of what I inherited. Tables are set up with good foreign key relationships, but no physical foreign keys. Initially, the live database was also used for testing, so there is quite a bit of bad data. Not overwhelming, but enough to stop me from creating the foreign keys I've wanted to create so far.

I've pretty much decided not to rewrite the system. I've read about too many rewrite nightmares to take that on. I'm now convinced that Access can do the job, even if it might not be the "ideal" choice.

I've also read about some nightmare "off the shelf" solutions as well, and as already mentioned, no system is ever perfect anyway.

I get paid an hourly rate, so the risk is all with the business. Hence my desire to try and warn them about what is coming. In a few years, if it turns out they've spent $300,000 to $400,000 Australian on a system (that works well), I would hate to think they could have got the same thing for $50,000 a year.

I'd still like to hear people's estimates for what I've described, and rest assured you'll see me reading as much as I can. Maybe even asking direct questions.

And hopefully I'll get to the point where I can answer some questions!

Oh. I also looked at Total Access Analyzer. That will definitely be getting some more thought.

Thanks again.

 

[Minty]

Without seeing the current system, it almost impossible to gauge the time it might take to "fix", as we don't know what works, what doesn't, and what new functionality might be required straight away.

A very basic method to get an idea of time scales would be to look at each form, and allocate time against redeveloping it.
If you have 50 forms and a allow an hour to "fix" each ones functionality, then it's 50 hours work. If you think each one would take 1/2 a day to work through, then it's 25 days work. There is no exact science to these things.

A really good exercise would probably be to find the most complex operational form and see how long it would take you to make it work, or replace from scratch. That gives you a bit of benchmark.

 

[GaP42]

As a project manager you will need to discuss the balance between Time, Quality and Cost for the scoped project with those that are in control of budget and the stakeholders. The control of the project under the agreed parameters is/will be your responsibility. It may be that some assessment can be made against bespoke vs off the shelf (which will require gathering requirements - and the basis of a Bible as Doc suggested.
It would seem that the scope is quite far-reaching and you may find it best to break it into stages which are more manageable.
Some of the description provided suggests:
1. The system is not heavily used - which may mean it is possible to roll out units that meet greatest need first
2. The live system was used for testing - that needs immediate intervention/correction - as you indicated it is causing problems in fixing the current system. I would hate to think how that operates/ how users rely on the data.

 

[CJ_London]

I have a client with a similar situation- a number of access apps written by an employee who has now retired. The apps are quite complex, interacting with Word, Excel, Powerpoint and Outlook together with some external apps utilising Rest API's. The apps are reasonably well constructed although not always the way 'I would do it'!

They looked at moving to an off the shelf system (or combination of systems) but none met their requirements (their business is fairly niche) without extensive and expensive bespoking plus additional costs of retraining. In addition they sometimes require rapid modifications as a result of external events - almost impossible with off the shelf systems.

I quoted a fixed price to familiarise myself with the apps, adding my own documentation and installing an error reporting system together with a fixed monthly management fee to monitor the system, fix bugs and provide minor modifications. Sometimes I 'win' on the time spent, sometimes they do. I note the hours I do and if the trend changes significantly, the management fee is reviewed. The management fee is also reviewed annually to account for inflation.

For more complex modifications I provide an estimate (not a quote).

 

[Pat Hartman]

I think everyone is confirming my thoughts that this is a significant project, requiring multiple developers. Please disagree if necessary.

You can't have multiple people working on the same database. It is next to impossible to coordinate. You need great discipline and standardization so that individual objects can be added to the master and automatically integrate. Everything needs to be a black box which means you have to be very careful if there are shared procedures to coordinate modifications.

Time and people are not interchangeable. If it will take you 2 weeks to do something. Adding a second person will not get the job done in 1 week UNLESS the task is actually n tasks that can be performed in isolation and do not have any interaction between the two people programming.

It is always good to have multiple developers who can step in and work independently but Access does not lend itself to this. So, if there are time constraints, the management will need to understand how much effort goes into the coordination. So, you add a second person. You don't actually have 2 workers. At best, you have 1 3/4 workers because 25% of the time of one worker is just management and integration.

but enough to stop me from creating the foreign keys I've wanted to create so far.

You need to fix the data. NOW. Use the unmatched query wizard for every relationship. Make the ones that don't have errors now. Then delete or fix the rest one at a time.