Double checking my logic?

I have to deal with the following situation.

I get a daily feed of data for several tables. This data is retrieved from the supplier by ftp in the form of CSV text files. I know I can load the files using "load data". The relational structure between the files and tables is simple: one file contains data for a lookup table. But the data in the lookup table is dynamic, potentially changing from one day to the next (though in practice, the rate of change is likely to be very slow). I will also need to maintain a history in such a way that any and all changes in the data can be fully audited. This means that, for auditing purposes, I will need to be able to extract valid time for each item in the lookup table. The only likely change in the data will be that occassionally items will be added or removed from the lookup table. None of the data already present in the database will be editable (data validation happens before I get the feed). The only analysis of which I have been told is done daily, as the feed is received. At present, no-one looks at earlier data (although I expect that will change as I create a better structure for the database to support audits).

I am trying to create a flexible design so that refactoring will be simple if and when the assumptions or practices need to be changed.

I know how to handle all this using a brute force approach, but I expect that approach will be painfully slow. So here is what I am planning.

1) create a suite of tables corresponding to the files in the feed, with the addition of a date/time stamp containing the date and time on which the data being processed was received.
2) create a suite of temporary tables corresponding to the tables created in step 1 (but without the date/time stamp)
3) load the data into the temporary tables
4) analyse the data while it is in the temporary tables, storing the result of the analysis in new tables
5) copy the data into the permanent tables, and add the date and time stamp for the data (this date/time stamp is not present in the files retrieved).
6) free the temporary tables

Now, this second last step is brute force, adequate for all but one of the tables: the look up table. If I stick with the brute force approach, the lookup table will waste a significant amount of space. This won't be much initially, but it is guaranteed to get worse as time passes and I'd expect the lookup performance to degrade as the amount of data in the lookup table increases.

Each record in the lookup table represents a product, and both the name and the product will have valid time intervals that may not be related. The name may change because the product has been renamed for whatever reason (and we don't care why), or the name may disappear altogether because the product has been discontinued. We can distinguish the two cases because each product has an ID that remains valid while the product exists, and the ID won't be in the data at all if the product is discontinued.

I am considering creating an additional table just to lookup product names, but with two date and time stamps. The first would represent the first time the product name appears in the data and the last would represent the last time the product name is present in the data. The first of these would be edited only once, and that is on the first day for which we have data. I am torn between updating the last of these every day, until the name disappears, or leave it null until the name disappears. leaving it null would save on space, but updating it with the current data and time should save time since it would not be necessary to execute a complex conditional on every product ever included in the database. If we update it only for those items in today's data, those that were terminated before today will not have their date/time stamp updated, so the two fields will always represent the time interval for which the name is valid. A similar logic applies to the product ID.

Have I overlooked anything that is obvious to you? Any gotchas I should be aware of?

What opportunities for improving performance do you see?

The ultimate objective is to have this fully automated from a shell script that is configured by our sysop to execute at a specific time every day Monday through Friday. Can all of the steps I describe above be implemented using ANSI standard SQL, and thus put into a stored procedure, or should I look at doing some of it in a Perl script or java application? I suppose I will have to have at least a basic Java application, or perl script, if only to connect to the database and invoke any functions I have created to do this.

Any suggestions would be appreciated.

Thanks,

Ted

R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Solutions
http://www.randddecisionsupportsolutions.com/