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'
);
dBforums
>
Accounting system - dedicated columns or do math?
07-24-10, 17:22
Linear Mode
