I am literally loading three types of values. Money (for financial buckets), bit fields (for determining if someone is a reseller or a direct seller and for determining if a product is new / used), and datetime for the start and end of product promotions.

The current table details are above in my first post. My dilemia is whether to create a fourth table containing the non-money datatypes, or to stick these non-money datatypes in the Contract table (where they really really don't work / fit) or the ContractDetails table (hence the question about sql_variant).

Since the bits and datetimes actually have an impact on pricing (rather than the Contract), I want to stick them in the Contract Details, but if I do them individually instead of all values in the same column, here's what I get:

Create Table ContractDetails (ContractDetailID int identity(1,1), ContractID int, BucketID int, BucketValue money, NewProduct bit, Reseller bit, StartDate datetime, EndDate datetime)

Which means I have 4 columns added for each individual bucket instead of just one set of for values for every contract. That seems to be an obnoxius waste of space IMHO. But I don't want to create a fourth table in my DW for 4 columns, a PK and an FK. That also seems to be a waste and then I have to do all the joins on it when I'm doing my reports, and everything gets really messy at that point.

So, Ideally, I stick Reseller, NewProduct, StartDate and EndDate in the Bucket table as individual bucket names (there will be more financial buckets than the ones I've listed below) and then just create records for ContractDetails that look the below:

BucketID BucketName

1 RetailPrice

2 ResellerPrice

3 Discount

4 Reseller

5 NewProduct

6 StartDate

7 EndDate

ContractDetailID ContractID BucketID BucketValue

1 12345 1 15.00

2 12345 3 1.00

3 12345 4 0

4 12345 5 1

5 12345 6 11/01/2007

6 12345 7 12/31/2007

7 78910 1 30.00

8 78910 2 12.00

9 78910 4 1

10 78910 5 0

11 78910 6 11/30/2007

12 78910 7 01/31/2008

Then I would pull all this data into one line for each Contract ID so it would look pretty on the reports. And SUM() the money buckets for Totals or do AVG() or whatever was required for the appropriate report.

Does that give you a better idea of what I'm looking at?