Phil

I'm quite certain there is no one "correct" answer. I was seeing if Jamie
has a recommendation to use a "middle" layer for the business rules, to keep
them abstracted from both the back-end data and the front-end screens/forms.

It would be simpler to use form-level validation, since there are
user-interface tools that assist. That doesn't necessarily make it
"better".

One approach to limiting choices to 1, 3, or 5-day courses would be to not
use a text box (into which the user could enter anything). Instead, if
there will NEVER be more than 1, 3, or 5, an option group could work. If
there's ANY chance there could be others, a small table of valid values and
a combo box on the form could do the trick.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"Phil" wrote in message
...
Hi Jeff

thanks for this i appreciate your reply. From your suggestion, I take it
that validation would be better through the form than through the tables.
how would I go about this and ensure that each course had the correct
number
of days at the moment I only have a text box which shows if it is 1,3 or 5
days

thanks

Phil

"Jeff Boyce" wrote:

Jamie

As you've pointed out, the validation (no "day 4" in a "3-day" course)
could
be handled at the table level with constraints added into the table
definition. This approach is consistent with our earlier "conversation"
about embedding as much business logic/validation into the table as
possible.

Aside from the issue of complexity (more difficult to achieve, since the
user interface does not facilitate this), I have a concern (just
realizing
it) that embedding logic and business rules into the underlying data
structure forces anyone using the data to agree to the embedded rules,
even
if they aren't aware that they are there! (again, with the lack of user
interface...)

I see an advantage using a more multi-tier approach, in which the data
is
just data, the user-interface is only that, and the business rules
occupy a
middle-ground? What are your thoughts?

Regards

Jeff Boyce
Microsoft Office/Access MVP
"Jamie Collins" wrote in message
oups.com...


On Nov 21, 12:51 pm, "Jeff Boyce"
-DISCARD_HYPHEN_TO_END wrote:
If I understand, you can have between 1 and 5 days for which you
need to
collect/record information. Instead of adding fields, then
modifying
every
query, form, report, macro and code snippet that refers to the
fields,
add a
single table to hold the "many" data. You've described what sounds
like
a
one-to-many relationship -- use the new table to show that.

Jeff,
I read the OP's spec as needing to go a bit further than this e.g.
it's
OK to add day four into the database if it's one of those five-day
courses but not for a three-day course.

Here's something I did for an earlier thread:

Approach 1: use referential integrity:

CREATE TABLE Students (
student_ID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Classes (
class_ID INTEGER NOT NULL UNIQUE,
seating_capacity INTEGER NOT NULL,
CHECK (seating_capacity 0),
UNIQUE (seating_capacity, class_ID)
)
;
CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL,
seating_capacity INTEGER NOT NULL,
FOREIGN KEY (seating_capacity, class_ID)
REFERENCES Classes (seating_capacity, class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
seat_number INTEGER NOT NULL,
UNIQUE (class_ID, seat_number),
CONSTRAINT row_level_CHECK_constraint
CHECK (seat_number = seating_capacity)
)
;

The pros include ease of implementation in Access because the
row-level

CHECK constraint can be replaced by a record-level Validation Rule.
The

cons include the otherwise-redundant repeating seating_capacity on
each

row and the burdensome need to maintain a sequence for seat_number...

Approach 2: 'hide' the need for both the repeating seating_capacity
and

the sequence of seat numbers in a table-level CHECK constraint:

CREATE TABLE Enrolment (
class_ID INTEGER NOT NULL
REFERENCES Classes (class_ID),
student_ID INTEGER NOT NULL
REFERENCES Students (student_ID),
UNIQUE (class_ID, student_ID),
CONSTRAINT table_level_CHECK_constraint
CHECK ((
SELECT C1.seating_capacity
FROM Classes AS C1
WHERE Enrolment.class_ID = C1.class_ID
) = (
SELECT COUNT(*)
FROM Enrolment AS E1
WHERE Enrolment.class_ID = E1.class_ID)
)
)
;



http://groups.google.com/group/micro...4430dc8b50ba8f

Jamie.

--