Structure database(s) dealing with different time points

Question

I have few data sets that give a score for a specific account at a specific point in time. So, account A might have a different score each quarter going back 5 years so. There aren't a ton of data points per customer and we're only dealing with tens of thousands of accounts at the most.

I'm new to tying databases together, so I'm curious about the best way to structure this do I need multiple database or can this all be in one that I can easily query to get all the values over time for a specific account? Seems like this would be difficult though.

Any expertise you can lend would be helpful.

Answer 1

Sounds like a single table in a single database. It sounds like you need three columns: account, date, score. The PRIMARY KEY might be (account, date).

10K rows is "small" for a table.

Answer 2

As long as you have a date field that marks the time the score changed, you can perform a versioned query to return the score that was in effect at any given time. Here is a recent answer that describes the query.