Guidance on Table Setup

[Auguy]

I am converting a VFP6 app to VFP9. At the same time we are switching from one person doing time entry to each employee doing their own time entry. There are about 50 employees with approximately 5 entries per person per day. 5 per day * 20 days per month * 50 employees * 12 months = approx 60,000 records per year. Not a great amount. Client invoices are created from these entries and once the time records are invoiced, they cannot be changed. Even though I can control what the user enters, etc. I guess it still bothers me that they are updating the "Live" table. Wondering whether to set up a user time entry table and a “Live” table and insert the records from the user time entry table to the “Live” table when the user releases them for billing. I know this means maintaining 2 tables. Anyone have any experience or guidance on this sort of thing?

Auguy
Sylvania, Ohio

 

[SBTBILL]

Sounds like a good idea to me. It is somewhat similar to the current/History tables used in ACCPAC PRO and Vision Point. They keep a record in both for open items which is a mess so I'd avoid that.

I like to put data in temporary tables and do all the read/writes to them. Then write back the changed records. A key point on this is having a unique key for each record. Otherwise, you end up with a deleted records bloat. I always call my unique number event. It is similar to the identity field in SQL. Haven't tried it but I believe 9 as a data type function that facilitates this. I'm old fashioned and do it all with code.

Bill Couture
http:\\

http://www.sbtbill.com

 

[Imaginecorp]

Even though I can control what the user enters, etc. I guess it still bothers me that they are updating the "Live" table.

Why?
If you have a good field validation routine, it does not matter if the user is hitting the “Live” table or a copy. I guess my question is, what exactly will you accomplish by creating two tables?

 

[Mike Lewis]

Auguy,

I can't really see the point of this. What benefit do you get from having this two-table approach? You've still got to validate the input data, and you still need a way of comitting or reverging all related records. I'm willing to be persuaded otherwise, but it seems to me you're just making things more complicated.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Auguy]

Thanks to all three of you for your comments and advice. I was thinking the client would feel better knowing his "Live" Time/Billing is isolated from the users scratch file and the "Live" file would only contain records that have been audited by the billing people. Obviously I can create rules and procedures to accomplish the same thing in one file, but clients sometimes have a different way of looking at things. Again, thanks!!

Auguy
Sylvania, Ohio

 

[SBTBILL]

I use multiple tables because I normally have at least a header table and a detail table. I work primarily in accounting software. This makes it easier to roll back a canceled transaction. Fir instance it a user puts in all the header info and then the sale fals through I don't want that record in the table. It would have to go in as a voided invoice or the audit trail would have a missing number. Like wise if a line item is removed or changed in initial entry I'd rather not have it in the permenant table. If it is edited after the transaction is saved but before it is posted and closed I want to see the old line item as voided with the new added. Thus I don't want any over writes. The itea being to never erase a transaction in a ledger but only line them out. Makes it a great deal easier to find out why an invoice total came out the way it did.

This concept is pretty standard in the mid-level accounting software I've seen.

Bill Couture
http:\\

http://www.sbtbill.com

 

[Mike Lewis]

Hi Bill,

Fir instance it a user puts in all the header info and then the sale fals through I don't want that record in the table. .... This concept is pretty standard in the mid-level accounting software I've seen.

You're right that the concept is a common one, and not just in accounting software. However, the usual way to deal with it is to use transaction processing (BEGIN TRANSACTION ... ROLLBACK ... END TRANSCACTION].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[SBTBILL]

Does rollback work with free tables? I thought it only worked with "Database" tables.

Also, I do a lot of this with SQL tables that I read into temporary tables and write back to later.

Bill Couture

 

[pthalacker]

Does rollback work with free tables?

It does in VFP9. See MakeTransactable()

pamela

 

[SBTBILL]

Great, I'm using 9 for some custom stuff, but my primary accounting apps are in 6-8 with a lot in 6 and 7. Checked help and it isn't there in 7. Doesn't look like it would help with the SQL stuff.

Thanks very much though. Always good to learn someting new.

Bill Couture