Defining Relationships

I can define my relationship as 1:1 or 1:N but what about implementing business rule restrictions etc. For example can i show that a table is Optional or that though the relationship is 1:M that there is a restriction of (1,8) meaning a minimum of 1 record, but a maximum of no more than 8 records? this notation is common but i don't see how to document it. I feel that i can't do all that i need to make sure my DB has a good design.

Thanks!

In article ,
says...
I can define my relationship as 1:1 or 1:N but what about implementing business rule restrictions etc. For example can i show that a table is Optional or that though the relationship is 1:M that there is a restriction of (1,8) meaning a minimum of 1 record, but a maximum of no more than 8 records? this notation is common but i don't see how to document it. I feel that i can't do all that i need to make sure my DB has a good design.

Thanks!


Hi Jacob,

Optional relationships are supported by setting the Required property of
the foreign key field to No. If you set it to Yes, Null will not be
allowed, and since you've enforced Referential Integrity, a valid key
will be required.

Relationship rules such as a maximum number of related records are not
supported by Access. You would need to write VBA code on the
application side to enforce that. I've found in practice that these
sorts of rules are rarely needed.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

Just out of curiosity, how (generally) could one enforce the max number of records (cardinality?) using VBA?
--
CMB from Omaha


"Armen Stein" wrote:

In article ,
says...
I can define my relationship as 1:1 or 1:N but what about implementing business rule restrictions etc. For example can i show that a table is Optional or that though the relationship is 1:M that there is a restriction of (1,8) meaning a minimum of 1 record, but a maximum of no more than 8 records? this notation is common but i don't see how to document it. I feel that i can't do all that i need to make sure my DB has a good design.

Thanks!


Hi Jacob,

Optional relationships are supported by setting the Required property of
the foreign key field to No. If you set it to Yes, Null will not be
allowed, and since you've enforced Referential Integrity, a valid key
will be required.

Relationship rules such as a maximum number of related records are not
supported by Access. You would need to write VBA code on the
application side to enforce that. I've found in practice that these
sorts of rules are rarely needed.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

In article ,
says...
Just out of curiosity, how (generally) could one enforce the max number of records (cardinality?) using VBA?


Well, it would be a little tricky. With forms bound to the main tables,
one might add code to the Before Insert event to check that there aren't
already the maximum number of records, and display an error. I can't
say we've done this before, but it should work.

A more robust way would be to use forms bound to work tables, where you
would process the work table records back into the main tables, but only
after you've checked the total number of records. This is more effort,
but more reliable.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

"=?Utf-8?B?Q01CIGZyb20gT21haGE=?="
wrote in
:


Just out of curiosity, how (generally) could one enforce the max
number of records (cardinality?)

The other way is by putting constraints on the referencing table: for
example if you have only eight seats at a committee, you would have a
Membership table that looks like

Membership
CteeCode INTEGER FK References Comittees
PersonID INTEGER FK References People
SeatNumber INTEGER CONSTRAINT 0SeatNumber AND SeatNumber9

CONSTRAINT pk PRIMARY KEY (CteeCode, PersonID)
CONSTRAINT lt8 UNIQUE (CteeCode, SeatNumber)

The ValidationRule on SeatNumber combined with the unique index means that
no committee can have more than eight members. How you allocate that seat
number is up to you, but remember that it is only an arbitrary counter --
unless, of course, you decide that 1=Chairman, 2=Secretary, etc etc!

Hope that helps


Tim F