If only it were that simple <g>...

I love the opening lines of this article:

Calculated Columns
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko4/

"You are not supposed to put a calculated column in a table in a pure
SQL database. And as the guardian of pure SQL, I should oppose this
practice. Too bad the real world is not as nice as the theoretical
world."

Normalization is A Good Thing but sometimes you have to at least
consider breaking the rules to get things done; the classic phrase is
"denormalize for performance" (noting that some respond that if your
normalized design runs like glue on your relational technology then
it's time to get some new relational technology).

One of the issues I see around here (but many miss) is where a
normalized design makes constraints harder to define, typically
requiring a table level CHECK constraint. Even some common design
patterns (e.g. a history table) require table level constraints to
enforce a basic primary key (lowercase); virtually everyone says that
every table should have a primary key -- or do they merely mean it
PRIMARY KEY (uppercase)? -- yet when I point out to someone that they
proposed design requires table level CHECK constraints I get seemingly
daft replies such as, "If this were SQL Server I'd do it with triggers
but it's not possible with Access", and "I'd do that with VBA in the
front end" and "It would be nice if Jet supported CHECK
constraints..." Now consider that some (most?) people who have
considered it think *any* solution that requires a table-level CHECK
constraint is 'out of left field'...

Let's me use the OP's example. The following structure I consider to
be normalised (5NF, I think):

CREATE TABLE Students (
  student_number INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Tests (
  test_ID INTEGER NOT NULL UNIQUE,
  max_score INTEGER NOT NULL,
  CHECK (max_score > 0)
)
;
CREATE TABLE TestResults (
  student_number INTEGER NOT NULL
     REFERENCES Students (student_number),
  test_ID INTEGER NOT NULL
     REFERENCES Tests (test_ID),
  UNIQUE (student_number, test_ID),
  score INTEGER NOT NULL,
  CHECK (score > 0),
  CONSTRAINT test_result_score_greater_than_maximum
     CHECK (NOT EXISTS (
        SELECT *
        FROM Tests AS T1, TestResults AS R1
        WHERE T1.test_ID = R1.test_ID
        AND R1.score > T1.max_score
  ))
)
;

The issue is the table-level CHECK constraint (the last named one; the
others may be replaced with Validation Rules). I'm comfortable with
it, despite the issues, but I know many are not. Therefore, I'll offer
an alternative with I consider to be denormalized but still effective:

CREATE TABLE Students (
  student_number INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Tests (
  test_ID INTEGER NOT NULL UNIQUE,
  max_score INTEGER NOT NULL,
  CHECK (max_score > 0),
  UNIQUE (test_ID, max_score)
)
;
CREATE TABLE TestResults (
  student_number INTEGER NOT NULL
     REFERENCES Students (student_number),
  test_ID INTEGER NOT NULL,
  UNIQUE (student_number, test_ID),
  max_score INTEGER NOT NULL,
  FOREIGN KEY (test_ID, max_score)
     REFERENCES Tests (test_ID, max_score),
  score INTEGER NOT NULL,
  CHECK (score BETWEEN 0 AND max_score)
)
;

There are no issues with the CHECK constraints here because all may be
replaced with Validation Rules. This is made possible by repeating
max_score from the Tests table in the TestResults. I think this is a
small price to pay for being able to utilise constraints (Validation
Rules and 'Relationships with Referential Integrity enforced' which
are familiar to and popular with most experienced Access users. That
said, hand on heart I'd personally stick with the fully normalised
design and would avoid actively promoting any denormalized design but
I do at least recognise that one cannot always be a purist about such
matters. Data integrity, on the other hand, I will never compromise
on.

Jamie.

--