Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    26

    Accounting system - dedicated columns or do math?

    I am making a purchase order system and am curious which would be the best way to design the database. From the way I understand, I have at least two options:

    1. Create columns for the budget amount, amount spent and amount remaining. Then when a user adds/removes a purchase order, use the PHP coding update those columns to reflect the new totals.

    2. Create just a budget amount column and use the MySQL query itself to add/subtract totals.

    I, of course, could be completely wrong with both these methods so any suggestions would be helpful. Here are my initial tables as if I were going with #2.


    Code:
    Financial Accounts
    
    create table accounts (
    id int unsigned not null primary key auto_increment,
    year_id int unsigned not null default '0' references years (id),
    name varchar(100) not null,
    budget decimal(10,2) not null default '0.00'
    );
    
    Purchase Order Info
    
    create table po (
    id int unsigned not null primary key auto_increment,
    account_id int unsigned not null default '0' references accounts (id),
    vendor_id int unsigned not null default '0' references vendors (id),
    po_number varchar(25),
    date_purchased date not null default '0000-00-00',
    date_created date not null default '0000-00-00'
    );
    
    
    Purchase Order Line Items
    
    create table po_items (
    id int unsigned not null primary key auto_increment,
    po_id int unsigned not null default '0' references po (id),
    qty int unsigned not null default '0',
    description varchar(100) not null,
    unit_price decimal(10,2) not null default '0.00',
    line_total decimal(10,2) not null default '0.00'
    );

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You use both methods. You use the first one until the fiscal year is closed. At that point you use the second. So, you do create columns for the amount spent and remaining, but you do not update them until the end of year close.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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