Re: Use a variable number of fields

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <jamiecollins@xxxxxxxxxx> wrote in message
news:1164207103.730435.199120@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


On Nov 21, 12:51 pm, "Jeff Boyce"
<JeffBoyce...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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/microsoft.public.access/browse_frm/thread/b03dbfc10b79f28f/1d4430dc8b50ba8f?#1d4430dc8b50ba8f

Jamie.

--



.

Relevant Pages

  • Re: Use a variable number of fields
    ... Microsoft Office/Access MVP ... REFERENCES Classes, ... CHECK constraint can be replaced by a record-level Validation Rule. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Use a variable number of fields
    ... REFERENCES Classes, ... CHECK constraint can be replaced by a record-level Validation Rule. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Use a variable number of fields
    ... CREATE TABLE Enrolment ( ... REFERENCES Classes, ... CHECK constraint can be replaced by a record-level Validation Rule. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Modelling Disjoint Subtypes
    ... constraints one should have are foreign key references. ... exactly prevents one from using a foreign key constraint to enforce ... enforces a constraint between columns in two tables. ... columns in N tables (where there are N-1 subtypes). ...
    (comp.databases.theory)