Quality Control Database Design Question

[Kozbot]

Hello. I am designing a quality control database to store data regarding my company's production runs. Here is a picture of the relationships http://i.imgur.com/yEt9yY3.png

The product table contains the product name with its QC specifications. The lot number table contains the unique production runs for each product. The QCEntries table records the data regarding the samples a QC tech is required to take every 20 minutes or so. The TestResults table contains the test results for each QC Entry (each product requires different tests, at different frequencies).The Test table has a list of the tests that will be performed

My issue stems from the fact that the majority of the tests performed return numerical answers, while a few are qualitative (Good, Fair, Poor).

How would you gurus solve this issue. Would you add another field in the test results table for the qualitative data?(seems wasteful) Would you simply keep the TestResults field as text to allow both numbers and text (will this present an issue with calculations and averages?) Make a separate Qualitative tests table and link it to the Entries table?

 

[CJ_London]

My own view is the better answer is to consider making the field numeric, so if the number is negative it implies a qualitative measure e.g. -1=good, -2=fair, -3=poor (presumes you do not have negative results) or a value less than 1, e.g. 0.1=good etc (presumes you do not have decimal results). This will almost certainly simplify subsequent queries.

The other answer is to keep the field a text field. The overhead will be if you do high volumes of calculations on the field since part of that calculation will involve (one way or another) converting it to a number. Won't be noticeable on relatively small volumes.

Or you can keep them separate - an empty text field does not take up any space

 

[ridonc]

Kozbot, I am making a similar DB. Would you mind sharing your final tables and relationships? I read through most of your previous posts...youve done alot of work my friend! Packing245 at gmail.com