3

I'm building a [Process-Tracker] thing In Excel. I would use Access but I can't guarantee that the End-Users will all have it.


So there will be a list of open cases, with a history of the previous steps in the process, the next step to be completed, key information etc. In a nice tabulated layout and sorted by next due date.

E.G.
[UNID] | Client A | Person B |,,,,| Step N | [task] | [Due Date] | [Other Info] |,,,,| Step 1 |..| Step 2 |..


I've spent a week trying to build one and realised that it needed way more abstraction.

I've come up with a new structure and I wanted to know, is it a good structural model or do I need to abstract the data into more layers?

I would really like to avoid starting from scratch again.


Design: The basic units will be CaseItem objects and ProcessStep objects.

CaseItem objects will have as properties:
A unique ID
An ordered list of KeyInformation
A list of the IDs of the associated ProcessSteps

ProcessStep objects will have as properties:
A bunch of general properties (description, due date, is completed, date completed, completed by etc.)
An index for their stage in the process.
A unique ID consisting of their index concatenated with the CaseItem ID E.G. Case ID = #00000001, Step 12 ID = #00000001,#12

There will be a [Hidden] Master Sheet storing all item data.

  • just use an off the shelf one en.wikipedia.org/wiki/Comparison_of_issue-tracking_systems – Ewan Oct 8 '15 at 15:17
  • I appreciate that that would solve the business-issue, but this is as much about me gaining experience in designing/implementing software as it is about the end result. – Kaz Oct 8 '15 at 15:20
  • Can't Access make standalone applications? – GrandmasterB Oct 8 '15 at 15:33
  • you've chosen a hard problem and a hard language to do it in if the main purpose is experience – Ewan Oct 8 '15 at 15:35
  • I didn't exactly get to choose. I've been programming for all of 6 months, one of the directors asked for it, and VBA is the only language I'm (currently) productive in. – Kaz Oct 8 '15 at 15:58
7

Let me speak from personal experience: this is a great sounding idea which you will regret doing.

Excel is just not a good database tool. Just some of the reasons:

  • Nearly impossible to have appropriate systems for permissions (which users can view/modify specific data. At least use xlVeryHidden if you go this route for your main "data sheet"). You will have to create a lot of custom dialog boxes or deal with really complicated/frustrating locking on sheets. This is a nightmare.
  • It's not designed to be a database. It's spreadsheets. Doing queries/etc on it can be done (you can write SQL vs spreadsheets if you hate your life want) but why? There are so many better tools, specifically designed for this purpose.
  • Shared workbooks are a disaster waiting to happen when many people update them frequently
  • It doesn't model table logic with queries/views easily
  • Each data view will have to be custom created (it does not have the easy table/query logic that Access does, for example. You can create forms/views much more easily with a TON more expanded logic than in Excel)

These reasons are made even worse in that many tools (such as Access) exist that are specifically designed to provide easy functionality to do all of the things you want to do with a database.

this is as much about me gaining experience in designing/implementing software as it is about the end result

You are asking about gaining insight into writing software. If you are a carpenter trying to learn how to put nails in a board, you wouldn't use a saw - you'd use a hammer, nail gun, etc.

Trying to make this application in Excel is the equivalent of using a saw to learn how to nail. It might work, sure. But you won't really learn how to put nails into a board the right way.

I've come up with a new structure and I wanted to know, is it a good structural model or do I need to abstract the data into more layers?

There are two answers here.

First, if you want to use Excel, you are going to want to actually minimize how well designed your database schema is. When designing a database you want it normalized as much as possible. However with Excel, you don't actually want to do this, because it creates a nightmare for actual implementation. You want to use range lookups and then have a single sheet for all the lookups. In a database these would be tables with foreign keys.

Second, I would strongly suggest looking into Microsoft Access for what you are doing. It will make this design process go from being insanely frustrating/complicated to very simple. For that route:

A unique ID consisting of their index concatenated with the CaseItem ID E.G. Case ID = #00000001, Step 12 ID = #00000001,#12

This is the sort of thing that Excel is going to make you do. Because you are thinking in a spreadsheet the concept of a unique key hardly makes sense to you (since you will "see" the key).

If you instead use a real database program you can make it:

CaseItem table
 - Unique ID
 - Misc information
 - CurrentProcessStep 

ProcessStep table (for definition of process steps. these items can be reused)
 - Unique ID
 - Misc information (description / etc)

CaseProcessSteps table (matches processsteps to caseitems
 - CaseItemID
 - ProcessStepID
 - Date related items

You could also have a separate table for date information if you'd like.

This is more like a database. Not a spreadsheet. Representing the above in a spreadsheet gets really hard conceptually because if you do it like this, you need a query to represent the data in "human readable" format - since it's all using IDs (like a database should) but Excel fails for that. You can do a SQL query or a bunch of index/match/vlookups, but this is a complete hacky thing.

This sort of setup is trivial to implement in Access. It's also a million times easier to make appropriate views to enter/edit this information in Access than Excel..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.