I just left the first design meeting for a database my superiors want me to create, to track documents used in the office. Many of these documents have due dates. The QA manager spoke up about one document that could have an extension to the due date. Due dates will generally be set by an initiation date plus some number of days. A separate table for extensions that relates back to the original record is what I'm thinking, but I'm getting hung up on how I deal with getting the original record to reflect the new due date. Any thoughts are appreciated.
Extended Due Dates General Question
- Thread starter DeanFran
- Start date
'Generally' is a bad word in databases. You need a system that works in all cases. So, you are going to need fields to store all these due dates. For the initial values, you can calculate them, but you can't hope to not store those values and always calculate them.
For example, suppose ProjectStart is the key date from which all due dates are initially set. When you start a new project, you could add a ProjectStart, then click a button which populates a table with all the related Due dates (OutlineDue=ProjectStart + 7, MgmtApprovalDue=ProjectStart +14, QuoteDue=ProjectStart + 18, etc.). Those would load to a table and then users could manually change them as needed.
- Local time
- Today, 13:31
- Joined
- Jan 23, 2006
- Messages
- 15,573
Where exactly are you in the analysis/design/development of this project?
Do you have a clear description of the business processes and rules involved? If so, could you share (even in overview) with readers?
There are a number of free data models at Barry Williams' site -- may help get started
Do you have a clear description of the business processes and rules involved? If so, could you share (even in overview) with readers?
There are a number of free data models at Barry Williams' site -- may help get started
The process is early. All the meeting amounted to was basically "These are the documents we want to keep track of." I'm familiar with most of them and how they function in our workplace. The use of the word "generally" was a poor choice. Lets take for example standard operating procedures. All SOP's are due for review once every 2 years. So an SOP's due date will be its latest approval date plus 730 days. Many of our documents follow these type of rules, and are easy to deal with. Some however don't. For example a corrective action/preventive action or CAPA document, will have a due date that is set by agreement between the author and the QA manager. If it is a simple thing, it might be a week, if the corrective action is more complex, it might be 6 months, again I can deal with this, but beyond that, when the 6 months is about to expire, more work may remain to be done, so the QA manager might issue an extension. In this case I will need to provide a simple mechanism to extend the due date, as well as track the status of the extension. Yet others like the SOP example might have fixed due dates and still might require extensions for some unforeseen circumstance. In this case, I will need to find a way to work around the rule I've built, to accommodate the extra time, as well as track the state of the extension. Is that helpful?
- Local time
- Today, 12:31
- Joined
- Feb 28, 2001
- Messages
- 30,495
There are two schools of thought on this kind of thing. If you have a small number of milestone dates and they are unlikely to grow in number, then you can store your milestones in the document record and just update them. HOWEVER, I don't ascribe to that model.
In terms of multiple milestones, I would have the document record as a parent with a one/many relation to individual milestones. When you create the document record, also populate the child table with as many milestones as are applicable, with dates computed based on the original document. Then you mark each milestone as "completed" when you complete it. When you need to extend a milestone for a document, you probably have to decide whether to extend the follow-on milestones as well. If you decide to update a middle milestone you can do a bulk update of all milestone dates still not completed for that document (which pushes back everything) or only update the single milestone (which squeezes time for the next milestone after the single extended item.)
You might also need to consider a record of extensions including whodunit and when, to what document and whether it was only milestone X or all open milestones for document Y. That is a detail you need to decide.
Here is the issue. EITHER you can take a really simple approach and keep milestones in a single record OR you can set it up to have a history-oriented layout. If you have strict auditing requirements, you want more details. If you don't have strict auditing, you can get by with less.
EMPHASIS: This is a design decision that needs to be made up front and with concurrence of the person who wants the stuff tracked.
In terms of multiple milestones, I would have the document record as a parent with a one/many relation to individual milestones. When you create the document record, also populate the child table with as many milestones as are applicable, with dates computed based on the original document. Then you mark each milestone as "completed" when you complete it. When you need to extend a milestone for a document, you probably have to decide whether to extend the follow-on milestones as well. If you decide to update a middle milestone you can do a bulk update of all milestone dates still not completed for that document (which pushes back everything) or only update the single milestone (which squeezes time for the next milestone after the single extended item.)
You might also need to consider a record of extensions including whodunit and when, to what document and whether it was only milestone X or all open milestones for document Y. That is a detail you need to decide.
Here is the issue. EITHER you can take a really simple approach and keep milestones in a single record OR you can set it up to have a history-oriented layout. If you have strict auditing requirements, you want more details. If you don't have strict auditing, you can get by with less.
EMPHASIS: This is a design decision that needs to be made up front and with concurrence of the person who wants the stuff tracked.
The_Doc_Man,
Thanks for the advice. I'll think about how to apply this to my situation.
jdraw,
I have a basic working knowledge. I've built a couple databases for different purposes here at work. For what its worth, I've also worked through an online course that included the basics as well as some more advanced concepts.
Thanks for the advice. I'll think about how to apply this to my situation.
jdraw,
I have a basic working knowledge. I've built a couple databases for different purposes here at work. For what its worth, I've also worked through an online course that included the basics as well as some more advanced concepts.
