Help - A Normalization Dilemma

Hi,
I have been able to normalize my db in all other aspects except one, and
ofcourse it is the most critical one (probably Murphy's Law). What I need to
do is store test results, but these results are variable in size from 1 up t
o
over 100. For example, a food manufacturer may only take 1 test sample for
salmonella for a specific product and production batch or 5 tests eg. salt,
sugar, added coloring, fat, calcium for another product and production batch
,
etc. up to over 100 tests for critical procucts.
Waht approach should I use with this, one large table with maybe 150 colums
(data type text 10 characters) or split it up some how.
Any pointers would be greatly appreciated.
Thanks,
RitakHi Rita
How is the table structure? is it like:
Product-1|Product-2|Product-3...
then, you can use this way:
Product ID|Product Name
1 |Product-1
2 |Product-2
3 |Product-3
4 |Product-4
Test Case;
Serial|Procuct|Test Result
1 |1 | Value
2 |1 | Value
1 |2 | Value
2 |2 | Value
3 |2 | Value
and so on...
Please let me know was this the one u are looking for?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"RitaK" wrote:

> Hi,
> I have been able to normalize my db in all other aspects except one, and
> ofcourse it is the most critical one (probably Murphy's Law). What I need
to
> do is store test results, but these results are variable in size from 1 up
to
> over 100. For example, a food manufacturer may only take 1 test sample for
> salmonella for a specific product and production batch or 5 tests eg. salt
,
> sugar, added coloring, fat, calcium for another product and production bat
ch,
> etc. up to over 100 tests for critical procucts.
> Waht approach should I use with this, one large table with maybe 150 colum
s
> (data type text 10 characters) or split it up some how.
> Any pointers would be greatly appreciated.
> Thanks,
> Ritak|||Your post is not entirely clear, I am not sure what the business model is,
COuld you try once again to describe what the actual data is?
Just as an example, though, if there are multiple different tests being run
for each sample drawn, and multiple samples drawn for each production batch
of a product, then you might use 5 tables
Products (ProductID, ProductName, ...)
ProdBatches (BatchID, BatchDateTime, ProductID[FK to Products], ...)
Samples (SampleID, BatchID[FK to ProdBatches] , WhenTaken, ByWhom, ...)
TestTypes (TestTypeID, TestName, Description, MinOKVAlue, MaxOKValue, ...)
TestResults (SampleID[FK to Samples] TestTypeID[FK to TestTypes],
TestResultValue, ...)
"RitaK" wrote:

> Hi,
> I have been able to normalize my db in all other aspects except one, and
> ofcourse it is the most critical one (probably Murphy's Law). What I need
to
> do is store test results, but these results are variable in size from 1 up
to
> over 100. For example, a food manufacturer may only take 1 test sample for
> salmonella for a specific product and production batch or 5 tests eg. salt
,
> sugar, added coloring, fat, calcium for another product and production bat
ch,
> etc. up to over 100 tests for critical procucts.
> Waht approach should I use with this, one large table with maybe 150 colum
s
> (data type text 10 characters) or split it up some how.
> Any pointers would be greatly appreciated.
> Thanks,
> Ritak