Rick said:
Any time you find yourself needing to aggregate across columns it most likely
means that your table design is incorrect. If you had a one table with a
one-to-many relationship to a second table and the second table had 30 ROWS of
records for scores then you could use a simple totals query grouping by the
Foreign Key and using Avg() to get the average.
Changing the design for the convenience of queries may result in a
significantly more complex design.
Simplifying the example, consider a table that holds Things. The
business rules are:
· each Thing must have exactly one 'minimum height value';
· each Thing must have exactly one 'maximum height value';
· for a given Thing, 'minimum height value' must be less than
'maximum height value'.
CREATE TABLE Things (
things_number INTEGER NOT NULL UNIQUE,
min_height_value INTEGER NOT NULL,
CHECK (min_height_value > 0),
max_height_value INTEGER NOT NULL,
CHECK (max_height_value > 0),
CHECK (min_height_value < max_height_value)
);
The 'must have exactly one' business rules are simply implemented using
NOT NULL. The above CHECK constraints are analogous to field- and
record-level validation rules. In summary, the business rules are easy
to implement as constraints using the simple tools available in the
Access user interface.
The average height calculation is also fairly simple e.g.
SELECT thing_number,
(min_height_value + max_height_value) * 0.5
AS average_height_value
FROM Things;
Scaling from two columns to 30 means extra typing but no real
complexity.
Now consider the proposed design where each 'height value' is a row
rather than a column:
CREATE TABLE Things (
things_number INTEGER NOT NULL,
height_type CHAR(3),
CHECK (height_type IN ('Min', 'Max')),
UNIQUE (things_number, height_type),
height_value INTEGER NOT NULL,
CHECK (height_value > 0)
);
The required query is now even simpler:
SELECT things_number, AVG(height_value)
FROM Things
GROUP BY things_number;
However, the above table constraints do not satisfy any of the business
rules e.g. a Thing could have a 'minimum height value' less than its
corresponding 'maximum height value' or the corresponding 'maximum
height value' could be absent from the table.
The only way I can see that the business rules can be implemented with
the revised design is via table level CHECK constraints e.g.
ALTER TABLE Things ADD
CONSTRAINT things__one_max_and_one_min
CHECK (NOT EXISTS (
SELECT T2.things_number, COUNT(*)
FROM Things AS T2
GROUP BY T2.things_number
HAVING COUNT(*) <> 2)
);
ALTER TABLE Things ADD
CONSTRAINT things__min_less_than_max
CHECK (NOT EXISTS (
SELECT *
FROM Things AS T1, Things AS T2
WHERE T1.things_number = T2.things_number
AND T1.height_type = 'Min'
AND T2.height_type = 'Max'
AND T1.height_value >= T2.height_value)
);
In addition to the complexity, there are several issues with
table-level CHECK constraints. For example, attempting to add data to
the table:
INSERT INTO Things (things_number, height_type, height_value)
VALUES (1, 'Min', 1);
results in the CHECK biting before being given a chance to INSERT the
second row (one could argue this is indicative of a single atomic fact
having been split across two rows, a serious design flaw). Without the
ability to defer the constraint, it must be dropped, the first row
inserted and the CHECK recreated before the second row is inserted, all
within a transaction (because you don't want the table to be left in a
state where the constraint has been dropped). Try doing that with a
bound form <g>.
The above example has two related values and has resulted in
significant added complexity when the 'attributes as columns' design is
changed is to 'attributes as rows'. The OP has 30 columns...
Let's face facts: you weren't proposing the OP write table-level CHECK
constraints, were you <g>? Validation rules are easier to write at row
(record) level rather than at table level, as demonstrated above. So,
without details about the business rules, how do you know the OP's
design can be changed in the way you propose without necessitating a
more complex design, perhaps one requiring table-level CHECK
constraints?
Jamie
--
