Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    27

    Table setup decision

    I'm setting up a checking/saving/budget database for myself to replace one I set up for myself some years ago.

    I've run into a dilemma while setting up the tables.

    Transactions have attributes like date, payee, amount, and type (debit or deposit). Of course, debit-type transactions have one payee and one amount. But for deposit-type transactions, I'd like to be able to record where the money came from when multiple sources and amounts make up the deposit. For instance, say I go to the bank to make a $100 deposit. Maybe $50 of the $100 was cash from returning something to a store, $20 was a rebate check for a DVD player, and $20 was a check from my employer reimbursing me for something, and the last $10 was misc cash from a drawer in my house.

    So that's a $100 transaction that has 3 payees (or sources, but since I want to pull suggestions from past transactions, they're really the same thing) with 3 amounts I want to record, and the remaining $10 that has no specified payee/source.

    What's the best way to set these tables up?

    Thanks.

    Part 2: What is the term for what I'm asking about? Is it "db schema"? just "db design"? "db structure"? none of these? all of these?
    Last edited by CVD; 02-06-10 at 12:13.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CVD View Post
    0 vote down star
    3 spit sideways horseradish


    Quote Originally Posted by CVD View Post
    What's the best way to set these tables up?
    with a comment/note text column, so that you can enter a comment on the transaction, outlining the sources or anything else that you wanted to make a note of about the transaction

    Quote Originally Posted by CVD View Post
    Part 2: What is the term for what I'm asking about? Is it "db schema"? just "db design"? "db structure"? none of these? all of these?
    database design is probably the closest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    27
    Quote Originally Posted by r937 View Post
    3 spit sideways horseradish
    lol. Fixed.

    Quote Originally Posted by r937 View Post
    with a comment/note text column, so that you can enter a comment on the transaction, outlining the sources or anything else that you wanted to make a note of about the transaction
    But that wouldn't be very query-friendly.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CVD View Post
    But that wouldn't be very query-friendly.
    please describe the sorts of queries you will want to run against this information

    it sounds like you already have decided which way to design this...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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