Re: Database design/normal form conundrum

From: Bernard Peek <bap_at_shrdlu.com>
Date: Fri, 26 Mar 2004 13:31:04 +0000
Message-ID: <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

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.

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. It's OK to build a denormalised structure if you have taken a look at what that might involve, and evaluated the risks.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.
Received on Fri Mar 26 2004 - 14:31:04 CET

Original text of this message