All Required Data Is Not Immediately Available - What to do?

What are some acceptable ways to deal with "required" data that is
unavailable at the time a row is added to a table?
Example: Lab tests are requested for a patient. Some results come in right
away and others come days later. All are eventually required in order for
the patient's lab records to be completed. It is unacceptable to the
customer to wait until ALL the values are available; they want to enter the
values AS they become available.
Obviously we cannot place a "NOT NULL" constraint on the columns in order to
allow for required data coming in late. I'd prefer to avoid having
application-level logic take care of this; but I currently don't see a way
around that.
Suggestions?
Thanks!"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>
Like this for example:
CREATE TABLE Patients (PatientID INT NOT NULL PRIMARY KEY, PatientLastName
VARCHAR(35) NOT NULL /* ... etc */);
CREATE TABLE PatientTestResults (PatientID INT NOT NULL PRIMARY KEY
REFERENCES Patients (PatientID), Result VARCHAR(20) NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||So it sounds as if you want to enforce data validation before a status
can be changed or checked. If the process of changing status is manual
(i.e, a technician clicks a button that "signs off" on the data), you
could use a trigger to verify that data is complete before allowing
that status to be changed.
If the process is automatic (you just want to check if the status is
complete), you could run a check process in your stored procedure to
look for NULL tuples and report that status is Incomplete.
Tossing some ideas around.
Stu
Fred Mertz wrote:
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter th
e
> values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
to
> allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!|||RE:
<< Tossing some ideas around >>
Just what I was looking for. Your ideas are helpful.|||Just a guess here -REMOVE the NOT NULL constraint?
CREATE a 'holding' table that accepts the incomplete data, and move it to
the 'real' table when complete.
Alternatively, set a default value. If char/varchar datatype default to
'N/A' or whatever makes sense. If numeric datatype, and if zero has
significance, then default to a predetermined 'magic number' that signals
the data is still missing, e.g., 999.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>