0

I'm a bit of a newbie with databases and database design, but I'm hoping someone can point me in the right direction. I currently have 14 monthly loan extracts, each of which contain all accounts, their status, balance and customer contact info as-of month end. Not knowing what to do, I imported each of the monthly files into Access with each table acting more like a tab from an Excel workbook. Laugh away - I now know that's not how it's supposed to work.

I've done my homework and I understand how to split up part of my data into Customer and Account tables, but what do I do with the account balances? My thought is to create a Balances table, create a relationship to the Accounts table and create columns for each month. This seems logical, but is it the best way?

99% of my analysis involves trend reporting and other ad hoc tasks - tracking the total balances by product type over time given other criteria, such as credit score or age. My intended use is to create queries to select the data I need and connect to it via Get & Transform in Excel for final manipulation and report writing.

This also begs the question "how normalized should my new database be?" Each monthly extract is cumulative, so a good 75% of my data is redundant contact info already, but how normalized should I go?

Sorry for ranting,but if anyone has any experience in setting up their own historical database or could point me in a direction that will get me on track, I would appreciate it.

Ron L
  • 51
  • 3
  • 11
  • 1
    Kudos to you for organizing your thoughts in a well formed manner. Unfortunately you probably won't get much help here on SO unless you can narrow this down to an actual code question, and not a system architect question like this. – mituw16 Mar 21 '17 at 14:25
  • Thank you, and I certainly appreciate your honesty. Finding solid advice or tutorials has been difficult - my issue is greater than a Product-Customer-Order situation but not on par with a multi-user environment transaction system. Good times. – Ron L Mar 21 '17 at 14:34
  • BCNF should be sufficient. Some developers stop at 3NF. First step: create your logical design, define your natural keys and your functional dependencies. – nicomp Mar 21 '17 at 14:38
  • IMHO it is a balancing act between normalization and ease of data entry/output. I agree with "normalize till hurts, denormalize till it works." Review http://stackoverflow.com/questions/47711/how-do-you-determine-how-far-to-normalize-a-database – June7 Mar 21 '17 at 17:36

1 Answers1

0

Best practice for transactional systems is close to what you expect: 1. Create a Customer table 2. Create an Account table 3. Create an Account Balance table 4. Create relationships from the Account to Customer, and from the Account Balance to the Account table.

You can create a column for each month, provided you have Year as part of the key of the Account Balance table. Even better would be to have the key for the Account Balance be Account ID and Date.

However, since you are performing analytics over the data, a de-normalized approach is not only acceptable -- it is preferable. So yes, you can (and perhaps should, based upon your use cases) put all the data into one big flat table and then compile your analytics.

TK Bruin
  • 472
  • 4
  • 15