Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Omaha, NE
    Posts
    2

    Unanswered: Need Table Design Expertise!

    Here is a table that will have too many null values:

    Asset Table:
    AssetID
    TotalAcctValue
    StockPercent
    Stock
    BondPercent
    Bond
    FundPercent
    Fund
    AnnuityPercent
    Annuity

    there are more similar columns. Some Assets will have only Stock,StockPercent. Some Assets will have Stock,StockPercent, Fund, FundPercent...etc....

    What is the best way to break up this table? I was thinking of putting a Stock, bond, Fund, Annuity bit field. and making smaller tables with Stock, bond, etc... But there should be a better way

  2. #2
    Join Date
    Oct 2003
    Posts
    18

    Re: Need Table Design Expertise!

    Originally posted by drobbins
    Here is a table that will have too many null values:

    Asset Table:
    AssetID
    TotalAcctValue
    FundPercent
    Fund
    AnnuityPercent
    Annuity

    there are more similar columns. Some Assets will have only Stock,StockPercent. Some Assets will have Stock,StockPercent, Fund, FundPercent...etc....

    What is the best way to break up this table? I was thinking of putting a Stock, bond, Fund, Annuity bit field. and making smaller tables with Stock, bond, etc... But there should be a better way
    Here's how I would do it:

    Asset Table:
    AssetID
    TotalAcctValue

    StockValues Table:
    AssetID
    StockPercent
    Stock

    BondValues Table:
    AssetID
    BondPercent
    Bond

    etc etc, you get the idea.

    Then you can get all the values with an outer join:

    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

    This will include all rows in Asset, but give you NULL for
    the columns from StockValues and BondValues for which
    there is no matching AssetID.

    /Hacker

  3. #3
    Join Date
    Oct 2003
    Location
    Omaha, NE
    Posts
    2
    That is what I was thinking, would it be too much to put bits in the main table, specifying if there were StockValues, BondValues. etc... I am thinking of performance.. And thanks for the response

  4. #4
    Join Date
    Oct 2003
    Posts
    706

    Re: Need Table Design Expertise!

    Originally posted by drobbins
    Here is a table that will have too many null values:

    Asset Table:
    AssetID
    TotalAcctValue
    StockPercent
    Stock
    BondPercent
    Bond
    FundPercent
    Fund
    AnnuityPercent
    Annuity

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  5. #5
    Join Date
    Oct 2003
    Posts
    18
    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..

    It all depends on your application I guess.

    /Hacker

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •