Originally posted by drobbins
Here is a table that will have too many null values:
there are more similar columns.
It sounds to me like there is an "AssetType" column that has values such as Stock, Bond, Fund, Annuity. And there is a "AcctValue" column and a "Percent" column.
The design-problem you've got here is almost the "repeating group" problem. But not quite. Here, the choice of columns you'd use, of itself, describes something about the Asset, and that's a no-no. There should be a column, such as AssetType, which describes every salient aspect of the Thing.
If you use outer joins, you don't need the bits of course. You just don't get any values for the assets that don't have e.g. a Bond value associated with them.
Of course a bitmap is still useful for filtering:
select a.*, s.StockPercent. s.Stock, b.BondPercent, b.Bond
from Asset a, StockValues s, BondValues b
where a.AssetID =* s.AssetID
and a.AssetID =* b.AssetID
where a.has_values & MASK > 0
Choose different MASK values depending on what you're looking for.
I.e if you have bit 0 for Stocks, bit 1 for Bonds and bit 2 for Funds, use
MASK=5 to get all rows where you have Stock and Fund values..