Re: Anyone have a trigger solution?

> You think you have it bad!
> I'm paid as a Q.A manager and I have been plodding along for 6 man
> years, (since 95), with 1 programmer dba,network admin,viri killer etc.
> (me!).

Wow!! You might just have it worse. It's a pretty close race though.

> I would suspect that the forms are basically good, try to turn the
> situation round, currently you are storing a huge amout of un-needed
> data.

I think you might be suspecting a little too much here. I joined back in July of 2000, and the only guy whose been on it has filled an entire box (the ones that hold about 8 reams of paper) with requests for data cleanup caused by problematic forms, requests to run scripts (there's a lot of features that are implement via "dba" type scripts - manual Bullcrap), and "bug reports". He said he stopped printing the requests at one point because he felt overwhelmed. That was 2 years worth (approximately).

I think we are storing a huge amount of INCORRECT data.

> you have a season_start & a season_end , I'm assuming that there are no
> dead days, I.E the season is continuous.
> therefore store only the days that have been allocated.

Actually, each SEASON is continuous per site, however, if there is a gap, a new season is entered, so YES, they can have multiple seasons in the same year, ie. a spring, and a fall. A summer season, then a fall, etc.

> I think you would be better off with nested tables, that way you can
> allocate your resources, and store the dates allocated in the nested
> table. it would only be a simple matter to scan for allocated days in
> the relevent resource, cleanup would also be a doddle, as would the
> update problem you are having.

That sounds like a neat idea. After all, we'd only be storing the dates already allocated. You couldn't create the same date again, right? Is there a primary key for this nested table to prevent that?

> try modifying one of your tables to add a nested table, then in the
> background write some code that transfers the data into the nested
> table, compares the results against the reservations, and if they are
> not the same , stick an entry into a table (e.g dba_evaluate) that you
> can browse, to pickup any errors.
> so that you would be double storing data, after a few weeks, you will
> have an evaluation period for free, where you can see the results.

That sounds like a slick idea. I might want to try that with the revenue aspect - it has a lot of holes in it. Maybe I'll store the true transaction history with the reservation and see if it matches the transactions in the other "base" tables. Like the vendor said when they wrote it... "It's a reservation system, not an accounting system. You can't expect it to be able to report on monies collected properly."

> then gradually patch your application, so that you take the data from
> the nested tables, eventually you will be in a position to, get rid of
> some of your tables ( reservations, bye bye forign keys etc)

I like the gradual approach. Right now it's a little unwieldy. I'm approaching that by rebuilding a lot of the database packages, and rebuilding the forms library. There's a lot of unused code, but even more cut&pasted code.

I'm hoping to consolidate ALL DML inside the forms to packages. I noticed in the few forms that I have cleaned up that this makes a tremendous difference - the next form REUSES much of the work I've already done. If it has a few new functions, I add more to the db packages or to the forms library. In time, it should clean up most of the issues. That's what I'm hoping for. I've removed a TON of duplicated code already, and I've only done about 6 forms. The more I do, the "more manageable" the system seems - and it doesn't seem quite as BIG as it once did - JUST MESSY!!

> you also don't say if you are using the advanced replication in the
> 8.0.6 ee version.

Advanced replication? I better go read the manual....

> whats a forign key?

Do you mean Foreign key? I must have had a typo.

Thanks again. BTW, what is your "real" email address? Can you respond to mine? Received on Fri Jan 19 2001 - 02:31:45 CET