Which of these table designs is better for performance?

Question

I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.

Here's what I know

• Company has over 2.5 million accounts
• Of these, they currently work an average of 200,000 per month (that changes with staffing levels, which are currently low)
• They have 13 different cost types they'd like to track, and they have warned that they might add more in the future
• They want the costs to be tracked daily
• Costs are not split across the entire inventory. They are either split across the # of accounts that are worked per month (200,000), or users can enter account identifiers to apply a cost to a group of accounts, or they could simply specify which accounts to apply the cost to.

My first thought was a normalized database:

AccountId
Date
CostTypeId
Amount

My issue with this is, do the math. This table is going to get huge quickly. Assuming all 13 cost types get applied to all worked accounts for the current month, that's 200k * 13 * N days in month, which is somewhere around a 75-80 million records per month, or close to a billion records per year.

My second thought was to denormalize it a bit

AccountId
Date
TotalCost
CostType1
CostType2
CostType3
CostType4
CostType5
CostType6
CostType7
CostType8
CostType9
CostType10
CostType11
CostType12
CostType13

This method is more denormalized and can create up to 6 million records per month (200k * N days in month), or about 72million per year. It's a lot less than the first method, however if the company decides on a new Cost Type in the future, another database column will need to be added.

Of the two methods, which do you prefer? Why? Is there another alternative that you can think of which would handle this better?

I am most interested in reporting performance, both summerized and detailed reports. The job that will spread the costs out over accounts will be run nightly when no one is around. A secondary concern is database size. The existing database is already almost 300GB, and I believe the space on disk is around 500GB.

The database is SQL Server 2005

Answer 1

A billion records a year isn't much.

With partitioning (per Costtype maybe) and archiving it is manageable.

The number of data items to store is still 200k * 13 * N. As columns, you'll get less rows per page and it will take more space than as rows. You may gain if "CostType1" is not a fixed length datatype, but it's marginal.

"KISS" as they say

Answer 2

While your design can certainly make a night or day difference, in this case I would focus more on indexes, including covering indexes as needed. I would also look at some of the tools that SQL Server gives you for dealing with very large tables, such as table-partitioning.

Think of it this way, even though there's 80 billion records in the table, with proper indexing, the ones that you're actually interested in at any given point will be grouped together physically on disk. Because of the way that data is organized in SQL server, data split by index boundaries may as well be in another table because it doesn't have to read the whole table to get at what it needs.

If you also choose to partition the table, you can improve access time and insert time.

Answer 3

I would normalize. We did cost accounting for customer account profitability at a bank and we generated over 250m rows of individual costs using hundreds of drivers which allocated by cost center or by general ledger or by various other techniques over millions of accounts each month.

For instance, the total cost of servicing ATMs was divided out among accounts which had used ATMs based on the relative amount of usage. So if $1m was spent servicing ATMs and only 5 customers used it once each and one customer used it 5 times, then that one customer cost the bank $.5m and the other customers cost the bank $.1m each. Other drivers might be a lot more complex.

Ultimately, you'll probably find it's sparse - certain accounts not getting costs from certain sources/drivers - and some accounts not getting anything. In a normalized model, those rows don't exist. In the denormalized model, the row exists, with some empty columns. Also, in a sparse normalized model, you should see performance improve, because the existence of a row is typically quicker to check (with covering index on CostType) than checking for all rows with non-NULL in a particular "bucket" (even with indexes on every amount column - which you can see starts to get very wasteful).

Answer 4

Regardless of the performance benefit, I would definitely go in favor of option 1. Option 2 would be robbing Peter to pay Paul, in my opinion.

Answer 5

I'd go with option 1, and then if reporting speed became an issue down the road I'd also add table 2, and populate it into a reporting database in some sort of automated overnight / offpeak process.

You could also then consider rolling up the daily table-2 structure into further weekly, monthly, quarterly, yearly rollups if warranted.

But, as I said, I'd also choose to store the 'raw' data in proper (normalized) form.

Answer 6

Considering the volumes you mention, I would go for the second option, but without the TotalCost. You could say that is still normalised.

---

Edit: as an alternative, and depending on your requirements, and the size of the AccountId, you could also consider the following:

AccountDate
-----------
AccountId  
Date  
AcDtID (surrogate key)

Costs
-------
AcDtID
CostTypeId  
Amount  

With that design, you could still add a denormalized TotalCost to the first table, and have it recalculated nightly, allowing to run some reports on the first table alone.

Answer 7

you should actually divide the firs table into two tables so that you can use a subquery and select the second row as a column, or many columns. it is more flexible that way and by that, you can get a result like the second one more easily.