Re: Designing a Database

I would think that a rather simple technique that can be used for promoting documents from one department to another would be to modify the two table approach in the following ways: The "user" table would contain their login id and their "department". Then, when a document is entered it's starting "department" is stored on the document. When this document is approved, it is moved on to the next department. You'll need to develop some scheme for moving the "department" from one to another (e.g. something as simple as adding 1 to the old department, or maybe a table with the departments listed so that it is used to show the progression of a document.) The key thing here though is that when a user fires up the application, their login id is looked up in the department table, and their department is tagged on the end of the construct statement. This allows multiple people to approve documents, yet their login id can be used so that you'll always know WHO approved the document. By reworking this scheme, you can give people the ability to approve at different levels, etc. It can prove pretty flexible. As far as archiving, there's no big thing in moving from one table to another, however unless you're processing a lot of information, I would suggest that you keep the detail data on line, rather than say, summarizing and archiving the summary data. By keeping the archive table basically identical to the "live" data, this gives you the ability to use the same reports and queries, by using "join" clause of a select statement, your cursors won't "know" the difference. Hope this helps, Will Hartung uunet!la4gen!villy