Post reply

Normalization Question

  • May 21, 2008 at 4:46 pm

    #207576

    I am designing a db to simulate a bank checking account application (practice project). All application activity will be in the form of db transactions based on users checking account number. I have designed a db with 1 table which contains all checking account holders transactions with fields: account number, transaction amount, type ('deposit' or 'payment') transaction date and optional (nulls allowed) check number and description. My problem is whether this single table is 3rd normal or not.

    accountnumber amount type date checknum description

    I can also conceive of a 4 table db containing one table of active account numbers only, one table for transactions (account number, date, checknum, desc) , one table for payments(account number, amount, date), and one table for deposits (account number, amount date)-- all of which are connected thru foreign keys (the account number). Can anyone help me by advising which of the two alternatives to use, or perhaps some better other schema?

    In either case above, all tables have an integer primary key, not shown above.

    Thanks. I'm a real newbie at this.

    Kayuca.

  • May 21, 2008 at 7:59 pm

    #818953

    4 table idea sounds good to me. Could be 3 tables because a deposit is nothing more than a credit transaction. Payments are certainly different and should be in a separate table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 21, 2008 at 9:11 pm

    #818965

    Jeff:

    Thank you. Second time you have helped me. I appreciate your time and effort.

    Kayuca

  • May 21, 2008 at 10:08 pm

    #818974

    No problem. Thank you for the feedback.

    Generally speaking, folks like to see running balances on their monthly reports. Take a peek at the following article for a super high-speed method to do such a thing... makes nightly processing a thing of the past for this type of thing. Will also help you decide on what the clustered index should really be on the "checkbook" type of table...

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply