Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
So get another disk. Disks are cheap. You can have 2TB for the cost of a meeting to argue about this. –  Peter Wone Oct 10 '11 at 23:04
add comment

migrated from stackoverflow.com Jan 26 '12 at 14:31

This question came from our site for professional and enthusiast programmers.

7 Answers

up vote 8 down vote accepted

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

share|improve this answer
3  
@Rachel I wouold definitely recommend implementing a partitioning schema with a data set this large. If they're focusing on month to month working and reporting then it's best to choose a partition key which can coincide with that mindset. Also, if you properly configure your partition you can easily switch data in and out from the table to staging tables which makes large data loads and deletions for rolling data sets a snap that takes seconds instead of hours. –  David Aug 17 '11 at 13:59
add comment

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.

share|improve this answer
add comment

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).

share|improve this answer
    
SPARSE - This is a very good point that makes all the difference. If it's sparse, you save space by normalising. Otherwise, not. But disk space is cheap, so personally I vote for maximum flexibility (normalised). –  Peter Wone Oct 10 '11 at 23:02
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I have TotalCost in there because the majority of the reporting is summarized, and I thought it would be faster to query a single value than add 13 different values. –  Rachel Aug 17 '11 at 13:51
    
Probably, but then you really introduce a transitive dependency. Will those records be ever updated ? or just written and then only read ? –  iDevlop Aug 17 '11 at 13:54
    
Records will get updated whenever a new cost is applied to that date range. After about a month it is unlikely that the total cost will get updated, but it's still possible due to things like yearly support fees. –  Rachel Aug 17 '11 at 13:58
    
Then each update would require 2 updates, and the TotalCost field adds a risk of inconsistency. –  iDevlop Aug 17 '11 at 14:02
    
Transitive dependency, but not necessarily a risk of inconsistency--a CHECK() constraint can guarantee that TotalCost is always the sum of costs. –  Mike Sherrill 'Cat Recall' Aug 17 '11 at 16:32
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.