This task is pat of our DIY Project: Reality vs daydreaming.
DIY Task Name: Design the database.
Task Goal: The goal of this task is to design a database that will hold the necessary data to perform the reporting.
Tip: This database has dual purpose: input of data and reporting and its aim is only to provide comparison of predictions vs actual balances, therefore making design "compromises" is more than welcome here as it is part of a Proof of Concept and most likely will be discarded later on. For this task enjoy breaking the rules, even the ones you don't know about :)
- Design a table (or tables) that will allow you to input all data that is necessary to do reporting.
- Analyze accounts statements and check which fields you have avialable (that can help).
- Read project again (a few times) and if there is any measure (numeric field) or description (string field) thend consider it in the design. You most likely will need it.
- The table (or tables) should allow to:
- Input balance predictions (for 1st of month).
- Input multiple accounts balances (taken from 1st of every month)
- Allow input of asset value or "balance". This is optional and should only be completed if you want an extra challenge.
There are many design options that I will share with you (you are welcome to come up with a different one):
- Excel spreadsheet - Excel spreadsheet is very suitable for this task and probably one of the fastest way to complete this project.
- Excel with PowerPivot - You may want to also import your spreadsheet into PowerPivot model. This may not have significant value for this project but may give you the necessary experience you are after.
- SQL Server table - a "proper" way to design databases however it may be an overkill for this project as you also need a way to input data and if it is more than one table you may find it a little bit problematic without some kind of User Interface (using access or asp.net) which ads to effort and complexity.
- Excel with MDS - Using SQL Server is not a bad idea if you use MDS with SQL Server 2012 as it contains an Excel Add-in so it is a good combination of both "worlds" excel and SQL Server.
We also need to consider structure of our design and we have some options:
- OLTP / Normalization - a really good choice for transactions databases but might be a bit of an overkill here.
- Star Schema / Denormalization - an easy design for reporting but for input of data may introduce data quality issues.
- Flat file - A simple design that is simpler than star schema but is less flexible which may not matter for this project.
Which ones should you choose? or maybe you should create your own onw?
Regardless of your choice, remember this is to gain experience so it really doesn't matter that much which choice you make, as long as you achieve project objectives and gain valuable experience :)
I recommend to estimate the effort of design and make a note of it. It's worth tracking your time and comparing it to your initial estimation so you can more accurately estimate effort in future projects (a valuable skill).
Where to start?
To help you get started with this task I will provide some useful links:
Good luck and take care