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..
