Re: Database Design - Urgent!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: alexcn (alexcn_NOSPAM__at_writeme.com)
Date: 11/08/04

  • Next message: Ultradiv: "Re: problem exectuing storded procedure" 
    Date: Mon, 08 Nov 2004 00:50:18 -0800

    Morning Hugo,

    Hope you had a good weekend, well I have spent most of mine racking my
    brains trying to think about how to take this forward - if you'll
    forgive me, I feel posting tables and example data will only serve to
    complicate what I am trying to explain so I will have one more try at an
    analogy but this time closer to the real application (as indicated in my
    email to you!)

    1) I have multiple accounts with multiple brokers for recording trading
    activity on financial markets, these accounts fall into distinctive
    groups based on the broker holding the account / trader using the
    account, and so on.
    2) Each trader executes trades in multiple products, each product also
    falling into distinct groups based on the product type / exchange on
    which the product is located and so on.
    Summary: Accounts ABC123 and ABC345 all within account group ABC and
    Products NASMSFT, NASDELL all within product group NASDAQ and LSEBT,
    LSEVOD all within product group LSE
    3) I have transaction header table, which contains the trade details
    i.e. buy/sell, qty, price, which form a one to many relationship on
    trade cash details i.e. Fees EUR, Fees GBP, Settlement EUR, Settlement
    USD and so on. Each trade will have multiple lines for each cash
    component representing the trade, the exact number is variable hence my
    requirement to have sub table rather than fixing the possible outcomes
    with extra pairs of columns (i.e. currency, amount) in the master table.
    Obviously the sub table has a FK to the header table
    4) Each trade therefore gets attached multiple lines of cash
    representation, the rates for calculation of which I need to look up in
    the tariff tables
    5) Each broker charges a uniform rate to settle each trade, and provide
    brokerage across all accounts within the account group (but sometimes
    there are exceptions for particular accounts), and each exchange charges
    different exchange fees per product group (and again there are sometimes
    exceptions) Hence the need in both respects to have the priority level
    to handle the exceptions per fee type achieved.
    Summary: a trade header record for NASMSFT will have PAIDEF (paid
    exchange fees) calculated from a tariff for PAIDEF attached to account
    group ABC, and product NASMSFT (i.e. level 2), and PAIDCF (paid clearing
    fees) calculated from a tariff for PAIDCF attached to account group ABC
    and product group NAS (i.e. level 4)
    6) These will be further calculated by way of the fact that the trade
    source is electronic or manual, and the volume of the trade will lay in
    a certain range (i.e. hence the need for the master and sub tariff
    table) The master contains the linking information and posting currency
    field, and sub contain the range constraints and trade type)
    Summary: I am pretty happy with the layout, and that it future proofs
    what may occur during the course of the business model and actually what
    you have come up with works separately for PAIDEF and PAIDCF, it just
    doesnt attach BOTH based on the different fee types

    I want to ensure, using the priority/level that we can have exceptions
    for each fee type recorded, i.e. all accounts ABC123 and ABC345 in group
    ABC would usually get charged the same rate for PAIDCF on NAS trades
    except for account ABC456 which, because a tariff exists at a lower
    priority (i.e. level 1) directly for the account ABC456 and NAS product
    group.

    Its almost like we need to evolve the query slightly so the rule
    matching scenario only applies to identical fee types (i.e. PAIDEF
    together and PAIDCF together) otherwise each transaction would pickup
    BOTH PAIDEF AND PAIDCF from different combinations of account or account
    group and product or product group.

    I hope this makes a little more sense now... sorry for the long winded
    explanation.

    Alex

    PS How much would it cost for your consultancy on this?

    Hosting @ http://www.host-junction.net
    Connectivity @ http://www.net-junction.com

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  • Next message: Ultradiv: "Re: problem exectuing storded procedure"

    Relevant Pages