Re: Database design/normal form conundrum

Mark Carter wrote:

> Normally, in one-to-many relationships, where you don't know how many
> is many, it is necessary to have two tables linked to each other. But
> supposing there were a fixed number of relationships. How would that
> change your view over using two linked tables over one big wide one?
>
> Here's the setup:
>
> A database exists whereby each record contains a data value. The data
> value has exactly five tests applied to it. A test consists of a
> pass/fail result, a user comment about the failure, and a user
> decision as to whether or not to ignore the error.
>
> So, that's five tests, consisting of 3 fields apiece, making 15 fields
> in all; and there is possibility that a couple more tests may need to
> be added.
>
> So which is better:
> 1. just have one table with lots of fields
> 2. two tables with much fewer fields, but with all the complications
> that linking implies

Design 2.

You've given the reason why in your problem description: "there is possibility that a couple more tests may need to be added". That's at the moment; in a year or two, there'll be another three or four or twenty or whatever.

Plus it is a lot easier to insert any test result into design 2 than it is into design 1/ Design 1 only makes any sense if you always have all 5 (or N) test results available all at once. And even then, you end up writing out some bits of code 5 (N) times.

Been there, done that, on an 18-way 'inspection report'. Don't go there.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/