Database Assistance Needed

Discussion in 'Computers' started by Brian Mansure, Sep 17, 2003.

  1. Brian Mansure

    Brian Mansure Second Unit

    Joined:
    Mar 15, 2000
    Messages:
    460
    Likes Received:
    0
    I'm a true beginner when it comes to creating databases and was hoping to get some help.

    I would like to create a database that will track daily family expenses that can be summarized daily, weekly, monthly and annualy.

    The data I want to record will be something like this:
    • Expense Categories (Mortgage, Utilities, Entertainment, etc.)
    • Specific Types of Expenses (dining out, groceries, daycare, etc.)
    • Date
    • Amount (spent, withdrawn, paid)
    • Type of Payment (credit, debit, cash, check)
    • Description of Transaction
    • Paid To

    Not too complicated, I just can't seem to put it all together in a logical data structure.

    Most likely I'll be using MS Access to create the database and ColdFusion for the frontend.

    I appreciate your suggestions and comments.

    Thanks,
    Brian
     
  2. Ryan Spaight

    Ryan Spaight Supporting Actor

    Joined:
    Jun 30, 1997
    Messages:
    676
    Likes Received:
    0
    First off, you'll save yourself lots of time and aggravation by just going out and buying a copy of Quicken or Money. But I get the impression you enjoy the DIY approach, so here's some suggestions to get started.

    First, create tables to define all your categories: Expense Categories, Expense Types, and Payment Types on the above list. These table should look something like this:

    EXPENSE_CATEGORIES

    Code:
     CODE DESCRIPTION SEQUENCE
     M Mortgage 1
     U Utilities 2
     E Entertainment 3
     


    And then for Expense Types (adding a field to handle the category):

    EXPENSE_TYPE

    Code:
     CODE DESCRIPTION EXPCATCODE SEQUENCE
     D1 Dining Out E 1
     D2 DVDs E 2
     M1 Movies E 3
     G1 Gas (Home) U 1
     E1 Electric U 2
     


    PAYMENT_TYPE

    Code:
     CODE DESCRIPTION SEQUENCE
     C Credit 1
     D Debit 2
     S Cash 3
     K Check 4
     


    In each of these tables, the CODE should be the unique, primary key.

    The purpose of these table is to have all your codes in one place, rather than defining them within your program code. If you want to add a new code later on, you only have to insert it into the appropriate table. (When you create your Cold Fusion forms, you should create the "pick lists" by pulling the relevant records from these tables, not by hard-coding them into the form. Use the SEQUENCE field to display them in the order you wish.)

    Once you've defined all that, the "meat" of the database will be the transaction table:

    TRANSACTION

    Code:
     TRANSACTIONID DATE PT EC ET PARTY AMOUNT
     0001 09/17/2003 K U E1 Con Edison -93.55
     


    would be how you'd store a check to pay your electric bill. The TRANSACTIONID should be automatically generated by the database (it's easy to do this in Access) and be your unique, primary key for this table. Also, whether the AMOUNT is positive or negative should be automatically determined by the payment type. Finally, you might also want to add fields to record the check number and comments. (And other things I'm forgetting.)

    You can then easily create reports/queries to see activity by date range, expense category, specific party, and so on.

    This is just scratching the surface, but building it like this from the start will eliminate pain later when you want to make changes.

    Ryan
     
  3. Brian Mansure

    Brian Mansure Second Unit

    Joined:
    Mar 15, 2000
    Messages:
    460
    Likes Received:
    0
    Wow, thanks Ryan for the detailed response.

    You're absolutely right about me liking the DIY method. This project will help me better understand and appreciate good data structure and tell me where my money is going every month.

    The "codes" that you described as the primary keys...
    I am to define these correct?

    Should they be alphanemeric or can the code be anything I'd like?

    Thanks again,
    Brian
     
  4. Ryan Spaight

    Ryan Spaight Supporting Actor

    Joined:
    Jun 30, 1997
    Messages:
    676
    Likes Received:
    0
    Yes, it's definitely best to define those yourself, so you pick something that makes some sort of sense to you. Make them whatever you want (as long as they're unique within their respective tables), making sure that you have enough permutations to deal with the likely number of items. (A two character alphanumeric code can handle 1296 items -- 36*36 -- which ought to be plenty.)

    After that, just have your program code deal with the codes (which makes constructing the SQL a lot easier and less error-prone), and display the descriptions to the user. (In other words, the user will never see or even know about the codes.)

    Ryan

    PS - Something I just realized while reviewing what I wrote above -- you don't really need the Expense Category record in the transaction table. The way I designed it (defining what category a type belongs to within the type table) an expense type can only belong to one expense category, so you can figure out what the category is based on the type.

    Of course, you might want to be able to have a type live in more than one category, in which case you'd want to leave the category assignment out of the expense_type table and put it in its own table, where you define a many-to-many relationship:

    EXPCAT_EXPTYPE

    Code:
     EXPCATCODE EXPTYPECODE
     E D1
     F D1
     


    Above, you have Dining Out (expense type D1) appear in both Entertainment (category E) and Food (category F). (There'd be no unique primary key in the data for this table, just have Access generate one.)

    In that case, you'd *definitely* want to leave the category out of the transaction table, so you could search for and find Dining Out under either Entertainment or Food.

    Isn't this fun?

    Ryan
     
  5. Brian Mansure

    Brian Mansure Second Unit

    Joined:
    Mar 15, 2000
    Messages:
    460
    Likes Received:
    0
    Yeah fun.

    This is exactly what I always have trouble with.
    Your example has made it much easier for me to understand so now I have a head start.

    So many people say databases are easy to create and are just "containers" for data but I feel if you really need to use that data, it better be stored in a properly structured and logical manner or it'll never make any sense.

    I could have made a one table database with all these defined columns fairly quickly and easily but the end result would most likely be an unusable database.

    Anyhow, I really appreciate your help Ryan.

    Have a good one,
    Brian
     
  6. John_Bonner

    John_Bonner Supporting Actor

    Joined:
    Oct 25, 2000
    Messages:
    664
    Likes Received:
    0
    I created my own family budget tracking system in Excel.
    Very easy to do with pivot tables.

    Make one sheet your "Tables" sheet where you have listings of Income, Expenses, Categories, Months. Range name each list for easier reference.

    Then make another sheet your entries. Each row becomes a separate entry. Columns are as follows: Date, Deposit, Withdrawal, Balance, Payee, Category, Month, Cleared, Comment.


    Enter the data as you would in your checking account register. Whenever you want to run a report, click Data, then Pivot Table and follow the wizard for creating a pivot table. The report can be put on a separate sheet and viewed any number of ways.

    I have about 3 years worth of activity on my entries sheet. I like to plan ahead by seeing trends in certain expenses. For example gasoline has really been going up in the last few months so for the coming budget months I have to allocate more. Electricity and Natural Gas flip flop between the seasons (gas high in the winter, low in the summer)(electric high in the summer due to A/C, pool filter and lower in the winter). Other expenses are harder to plan for like gifts, holidays, car repairs, etc. I also have a category called Medical which includes doctor visits, prescriptions, lab bills, etc. Makes it easy at tax time to track unreimbursed medical expenses.

    I've tracked my checking account in Excel for at least 10 years now. I never use a checking account registed. The only reason I use Excel is because I work with it every day and I'm more familiar with it than Access. I've been meaning to learn Access and maybe I'll try Ryan's method as a project.
     

Share This Page