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

Reply via email to