Re: Database design/normal form conundrum

"Bernard Peek" <bap_at_shrdlu.com> wrote in message news:Rtphzv4YCDZAFwD+_at_shrdlu.com...
> In message <d3c9c04.0403260239.20a57d3f_at_posting.google.com>, Mark Carter
> <cartermark46_at_ukmail.com> writes
> >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

#2. The design sounds quite simple, so I don't see the "complications."

> Remember the KISS principle (Keep It Simple, Stupid.)
>
> If you know for sure that there will always be exactly 15 fields
> required then a single table is the obvious answer. Everything depends
> on how well you know the data. And remember that databases don't exist
> in isolation, they exist as part of a system.

No, they don't - they exist as part of a business, and as part of systems which haven't even been envisioned yet. So it's critical that they have some meaningful structure of their own, apart from whatever your immediate processing needs are.

A more flexible design will let you vary the number of tests, and possibly even use it for additional data sets in the future which need to be tested, and won't necessarily have 5 tests.

Furthermore, any queries or reports you do which look for data in any of the test responses will look like this:
SELECT value WHERE response1 LIKE '%confusing%' OR response2 LIKE '%confusgin%'...

> Who decides that there are only three tests? Is there likely to be a
> change in legislation that imposes new tests? Could there be a change in
> management of the organisation, will that have any effect? You need to
> look at the application and the organisation. What is the probability of
> a change in the way the tests are administered. Will that affect the
> data stored?
>
> Only you can make the final decision because only you are close enough
> to the problem to be able to evaluate all of the risk factors. You have
> identified the conflict between a theoretically best structure using
> multiple tables and a more pragmatic design using just one.

I disagree that the single-table design is more pragmatic. You'll save nothing by using it, and the minute your requirements flex in any directions, you're condemned to much more work. It's foolish to attempt to shave a tiny amount of time based on an assumption of absolute requirements freeze.

> It's OK to
> build a denormalised structure if you have taken a look at what that
> might involve, and evaluated the risks.

I disagree, in most cases but certainly in this case, given only the decription you have above.

• Eric