This is a simple normalized database that might work for you :), depends of the quantity of records you are going to register and other things.
[Table Entities] id int auto_inrement PK entitty_name varchar [Table HoursUsed] id int auto_inrement PK entity_id int FK day date quantity int (you could use reals instead of integers if you work with 1/2 hour) [Table HoursPurchased] id int auto_inrement PK entity_id int FK day date quantity int (you could use reals instead of integers if you work with 1/2 hour) --------------------------------------- OR ypu could have something like like this: [Table Entities] (Same as above) [Table Hours] id int auto_inrement PK entity_id int FK day date hourtype int (this could be 0 for purchased, 1 for used, or something like that) quantity int (you could use reals instead of integers if you work with 1/2 hour) For calculating the hours remaining you just have to do a simple query. You could have a table with the hous remaining initialy set to the value of the hours purchased and decremet the value each time you use hours, this might have some impact in performance, i dont know which might be better, you have to do some math to know :) Hope this helps. ----- Original Message ----- From: "rogue" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 02, 2004 3:23 PM Subject: [PHP-DB] Bank Account Register > Hi all, > > Please CC me on any reply - I get the digest :) > > I am working on a project where I need to implement something that most > resembles a checkbook register, where I am tracking pre-paid hours > bought vs. hours used on-site. > > I am getting a bit stuck on the best structure for the mysql table, and > would like some suggestions. > > In the end, I need to be able to run a report where by date it displays > hours purchased, and hours used like in a check register: > > > DATE DESC. BAL. > 2/23/04 Purchased 10 hours 10hrs. remaining > 2/24/04 Job 2 used 5 hours 5hrs. remaining > 2/24/04 Purchased 20 hours 25hrs. remaining > > > Like so... > > Would it be best to have 1 table with a field for transaction type and > keep all the ins and outs in one table, or have two tables one where I > track purchased hours and the other where I track used hours? > > Thanks, > Rogue > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
