Normalization and historical data

Question

Before I describe my problem, I'd like to get a couple things out of the way:

1. I'm an experienced (though not expert) database designer. I believe I have a good grasp of the relational model.
2. I don't have such a firm understanding of the relational model that I know exactly what to do in every situation. I'm still learning.

Let's say we get an Excel spreadsheet once a month from a bank, but not always the same bank. The spreadsheet has just six columns: bank name, account number, account balance, customer (accountholder) name, customer SSN and accountholder address. Each row has a different account number and no account number is listed in more than one row. We want to import this spreadsheet into a database and, at any time in the future, say, "What was John Smith's address on October 13, 2010?"

For simplicity, let's say that every customer only has one address and that every customer can have zero or more accounts. And just for a second, let's pretend that we only have to do one Excel sheet import EVER, which is a silly premise, but bear with me. If that's the case, the following design would suffice:

bank
--------
id
name

account
--------
id
bank_id
customer_id
number
balance

customer
--------
id
name
ssn
address
city
state_id
zip

state
--------
id
name

The rest of my question is based on the premise that you agree that that schema is "correct", so hopefully you're fine with it.

Now, that would be fine if we only ever did one import, but we'll be doing 12 imports per bank per year. Here's how I was thinking of accounting for that:

bank
--------
id
name

account
--------
id
import_id
bank_id
customer_id
number
balance

customer
--------
id
name
ssn
address
city
state_id
zip

state
--------
id
name

import
--------
id
date
excel_file (blob)

Now every account is tied to an import and we can say with certainty things like "Account 12345 came from import 572 on 10/13/10." It gets potentially a little more ambiguous when you look at, say, the customer table. Since there are less rows in the customer table than in the account table (because some customers have multiple accounts), we don't have that one-to-one relationship between customers and imports like we do for accounts and imports. I know there's no data loss and there's no loss of data integrity, but it still feels like some sort of sacrifice somehow.

My question is (and this may be too open-ended): Do you think this is a good way to store the data? Would you have done it differently?

Edit: there's an important way of thinking about these entities that you have to be aware of. Don't think of an account as one account that exists over time. Think of an account as a snapshot of an account at a certain point in time. Therefore, account 12345 with balance $100 is NOT the same account as account 12345 with balance $150. Yes, both records are tied to the same bank account in the real world, but what I'm storing is a snapshot of the account at a certain point in time. Similar (but not identical) situation with customers.

Answer 1

1. As each import is tied to a specific bank, I might consider putting bank_id in the import table and leaving it out of the account table.
2. If you are wanting to account for historical address data, and you are getting that data exclusively from your imports, you might add the address fields to the account table and remove them from the customer table. Granted, doing so can lead to duplication when you have the same address for multiple imports. If you care a lot about that, you can add another table, maybe "address", likely with a compound primary key of customer_id and address_id. Then, your import table adds the address_id field, and your import code would need to check whether the address already existed.

Answer 2

In general, the design looks good to me.

Does the import/import_id itself have any meaning outside of storing a date? If not, I see no reason why you shouldn't exclude the table altogether and put an import_date on the account table.

Also, if you want historical address information, you're going to need the import_id (or import_date :) ) on the customer table as well.

Update

As noted in the comment, adding the import_id would not account for historical address data.

What you would need is some sort of customer_history table, which would store any of the data that could change, and would link back to the customer table via foreign key.

customer
------
id
first_name
last_name (assuming name wouldn't change--it certainly could)


customer_history
-----------------
id
customer_id
import_id (or date)
(address fields)

If the details of the account could change over time, you would need a history table for that as well.

Answer 3

i would be cautious of the idea that a customer has only one address. (this is not true in my real life experience). You will either need to store the most recent address you get by updating the customer on each load, or you should consider splitting off the address to a new table and linking it to the customer - perhaps with begin and end dates to show you when you thought that address was valid.

I think also i would not put import_id on the account. If you do this, you will get lots of rows (x12) for each customer-to-bank connection. Not what you want, i think. instead, you could put an account-to-import link table to let you know that this account was listed in one or more of these imports.

Answer 4

I'm sorry, I can't reconcile the statements "each customer has only one address" and "we want to say 'What was John Smith's address on October 13th, 2010'". Are you suggesting that on each import, you'll create a new customer record for each person found in the import? If so, how will you know that John Smith in one import is the same John Smith from another import if the account numbers are different?

And if you reuse the same customer record for the same customer (which seems correct for me) where to you find prior address information?

[After comments and amendments by the poster]

Okay, you're almost there. You do need to add the customer address to the Account table (which should really be renamed AccountImports or something like that). That's because each import might have a different address.

Storing the address in AccountImports is slightly un-normal if the address frequently stays the same from import to import. If so, you can add a CustomerAddressHistory table. During each import, check the latest address for the SSN in CustomerAddressHistory and, if not the same as the import, add the new address to a new record in that table.

Answer 5

No clue what DB you're using, but here goes: I would NOT have stored the import as a blob, as it impedes your ability to link with your existing data because you have to process the blob as the type of file you expect it to be before you can join it with any of your other data. Import the data directly into your import table along w/ the id and date field you already have. Put a key on id, then a unique compound index on date, bank, and account to prevent same-date dups.

If you know for certain you will only ever have 12 imports a year (months, I presume?), you could increase integrity by creating two calculated fields, one for date_month (to hold JUST the month), and one for date_year (to hold JUST the year), and then create a unique compound index on bank id, account, date_month, and date_year. This would prevent accidental re-imports for the same month's data on different dates, like if the import for Oct was done on Mon, then someone did it again on Tues. It would also prevent "oops I clicked the button again" or "oops, I imported this month's data as last month's" scenarios. To speed up the checks on the calculated fields, put unique indexes on date_month and date_year.

If you want your customer table to always reflect the current address without any fuss, make address a calculated field that does a lookup into your import table by customer account (or SSN or etc) and picks out the TOP 1 address sorted by date DESC. If you want queries on or including the address field to be faster, put an index on it.

Answer 6

I would make a new table Called CustomerAddress and move the address informaiton out of customer into this new table

Then on the Account table and CustomerAddress table add 2 new columns StartDate and EndDate

That way you get to keep a single row of a customer overtime and can easily track each of the customers accounts and Addresses overtime. it gets too confusing if you try to keep mutiple copies of a customer.

Answer 7

There were a lot of good points here but nothing that was really an answer that I was 100% satisfied with, probably because it was a badly-formed question. In order to "improve" my accept rate, I'm "answering" my own question.