Evan,

I worked in the clinical trials area for a couple of years. Everything has to pass FDA regulations which are pretty stringent.

We were allowed to code non-specific dates according to "rules" (SOP's). Everyone knew of the rules, including the trial sponsor.

For non-specific dates like "patient had an MI in 1984", we were allowed to code {01/01/1984}. For patient had an MI in July, 1984, we were allowed to code {07/15/1984}.

Every field could have an entry in the "NAND's" table. NA or ND could mean "not available," "not applicable," "not determined," "no data," and so on. The NAND's table had fields PatientID, TableName, FieldName, and Value, but since NA and ND were pretty interchangable the mere presence of an entry was all that mattered.

Yes, there was A LOT of programming involved. Confidence was maintained because data were "fudged" according to specific SOP's. This was good enough for the US FDA to approve a new drug (they are not easy to please), and is pretty common among other clinical trials organizations.

What to do in cases of missing or incomplete data (can't bill because exact time of tx is not noted) is a business rule, to be determined by the user. Programming to keep track of it all will give you "job security". (Job security = too much work ;-))

We had a lookup function which was passed the TableName (a table corresponded to a page of the form) and FieldName for the datum in question and looked in both the data table and the NAND's table for the presence of an entry.

You could either have a separate table as we did, or a corresponding logical field for each main field. (cPtCity C(30), lNDPtCity L)

We also had fields which were not allowed to be NA or ND. For example, did the patient expire during the study period? You had to know that he did or didn't! Your "customer" would have to determine what was required.




>I didn't get much of a response last time, so I will rephrase my problem.
>
>How should I record non specific data into a database that requires specific values. Databases by nature require specific data. Example you can but in June 1/99 but not June /99 or just 99 unless you write a lot of code to allow this.
>
>How should one allow this nonspecific data into the database. There are two sides to the issue
>
>1) You know the data must be there (example: the product was sold in June)
>2) You know the data will be wrong if you guess it (example: If you guess at June 1/99 you only have a 3% chance of being right).
>
>How can this "non-specific" data be put into the database with the least of amount of trouble. Some ideas I have:
>
>1) Don't put it in.
>Pros: data in database is 100% accurate
>Cons: data in incomplete (this may violate laws, ethics, or have a ripple effect to places like accounting)
>
>2) Put the data and and make a best guess.
>Pros: Easy.
>Cons: Data is wrong. Lowers confidence of accuracy of data. May violate laws or ethics. May have a ripple effect elsewhere. Example: what if person returns the product before your best guess as to when it was sold.
>
>3) Provide a method of allowing incomplete data.
>Pros: System is flexible
>Cons: Could require tons of work to accomplish this. The abillity to work with and report on the data could become very complex. Users may abuse this feature.
>
>-------------
>Examples of these kind of situations where we only know the approximate data.
>
>1) Salesman takes stock from warehouse to customer without proper documentation, gets a check from the customer (without a date on it), promptly forgets about the check and discovers it 3 months later. We can recreate the invoice because we know (for instance) that the salesman started at the company in June 17, and the customer returned the product in Sept 4. We know the invoice has to between those dates. What should we do?
>
>2) Patient arrives in hosp with broken arm. Is x-rayed then released. Tech didn't write down the time of the x-ray so we can't bill. We do know that the patient was admitted at 10am then released at 4pm.
>
>3) A paper form for bill of sale was submitted for a toaster from a remote store. New clerk handwrote the date, we can only make out that it is June/99 but not the day. The problem is that manufactor's warranty is 90 days from the time of purchase.
>
>and on...