Question About Temp Tables

[Rawb]

Hey all!



Because of the HORRIBLE design of our primary work database, I've started using temporary Tables to store data on some of the more complex processes. I was wondering when, generally speaking, temporary Tables should be used and how they should be designed.

• How difficult/complex do you let things get (in terms of programming/Querying) before you should use a temporary Table?

• Should temporary Tables follow standard normalization rules or should you "cut out the middle-man" so to speak and set it up to hold EXACTLY what you're going to end up using? For example, if I have a 25 character text description field, should I use the ID pointing to that field and set up a relationship, or go ahead and just save the actual description? What if I end up using this same description in multiple (say virtually all of the) Records saved to the temporary Table?

• What about saving user input to the temporary Table (instead of trying to save it to a variable - or keeping the input Form open and reading from there)?

Since the database is already horrible designed, and this is a temp table that will be wiped after each use, being less than perfect isn't a huge issue, but I'd still like to hear your input.

[NTC]

Temp tables are avoided, in terms of not being the 1st method for a solution. But one can't be too dogmatic.

I don't see normalization as an issue at all - they are tactical scratch pads designed to the need.

I have found that making/deleting the table object itself causes bloat; and instead I keep the table permanent and do append/deletes of records only. Although I've seen advice just the opposite on this point.

In one case I had situation where there was a very advanced query before a report would generate. The user had several versions of the report they viewed - and for each it would rerun this elaborate query. The delay annoyed them. So I instead wrote the query result to a temp table & resourced the results. Then after the 1st initial delay - all future report views were instantaneous.

I also use temp tables regularly in imports where the data, once imported, needs some manipulation before final append to the real table.

So when one is solution oriented sometimes a temp table is needed, but generally I will always work with query record sets alone. Hope this helps.

[Rawb]

I agree that temp Tables should be avoided whenever possible. Unfortunately, the database we use here is so unspeakably - horribly - designed, that I routinely have problems unless I use a temp Table. This will be the fourth temp Table solution that I've set up since I started working here.

As for the deleting/recreating the entire Table itself, I agree with you. I always have the Table in the system, I just delete the Records.

As for the question of normalization, what about a situation like this:

Example TempTable

ShopOrder|LineNumber|GroupNumber|PartNumber|OptNumber|Description
123456|1|12345|123|01|1/8in Clear Glass
123456|2|12345|123|01|1/8in Clear Glass
123456|3|12345|123|01|1/8in Clear Glass
123456|4|12345|123|01|1/8in Clear Glass
123457|2|12345|123|01|1/8in Clear Glass

In the above example, the first three fields of each Record are combined to form the primary key (so they would have to be there no matter what). And, because the PartNumber may not actually be the PartNumber listed in the shop order (long story), it really needs to be there too. But what about the remaining fields?

OptNumber is a number that the user enters at execution time. And, as long as GroupNumber doesn't change, OptNumber won't change either. Should OptNumber be included in the Table or not?

Also, what about the Description Field? That can be found by looking up the appropriate PartNumber in the GlassMaster Table. And, like OptNumber, it won't change either.

Should I leave those out and just "calculate" them by referencing the appropriate Tables/Forms or should I go ahead and include them?

[NTC]

they're there. so leave them. normalization is the ideal - starting with a clean slate. but you aren't starting with a clean slate. so simplicity counts too. to add extra manipulation to remove things for the sake of theoretical normalization doesn't strike me as wise. keeping it simple counts too. so I would just leave them.

memory is no longer an issue and so there really is no down side to keeping them that I see.

[Rawb]

Good point. I hadn't really thought about it that way. . .

Thanks!