Referential integrity?

[helpneeded]

Hi,
I have 3 tables in database :
- ORDER
o OrderID
o OrderTYPE : A, B, M
- DETAILEDORDER
o DetOrderID
o OrderID
o ProductID
- PRODUCT
o ProductID
o ProductTYPE : A, B
o ...

Data integrity is on and relationships look like:
1-many between table ORDER and table DETAILEDORDER
1-many between table PRODUCT and table DETAILEDORDER

I need to achieve the following:
- ORDER of type A: has to be composed of products of type A only
- ORDER of type B: has to be composed of products of type B only
- ORDER of type M: ca be composed with either products (type A or B)

Is this possible through referential integrity? If not, is there any other
way I can achieve this?

Many thanks for your help,

Lionel

 

[Allen Browne]

DO not store OrderTYPE as a field in the ORDER table.

It is something that should be derived from the contents of DetailedOrder.
That is, if DetailedOrder contains only products of the A for a particular
OrderID, that defines it as an order of type A.

 

[Rick Brandt]

DO not store OrderTYPE as a field in the ORDER table.

It is something that should be derived from the contents of
DetailedOrder. That is, if DetailedOrder contains only products of
the A for a particular OrderID, that defines it as an order of type A.

I would disagree and say that it depends on what the definition of a Type A
order is. If the definition of a Type A order is an order that happens to
contain only Type A parts then I agree with your statement.

If the definition of a Type A order is an order that MUST contain only Type A
parts then the parts themselves cannot be used to satisfy the type. You need
something that indicates the order type so the the parts added can have the Type
A restriction imposed upon them. In that case having a Type field in the order
table is the way to do that.

 

[helpneeded]

The definition of a Type A order is indeed an order that MUST contain only
Type A
parts. But how can I do that?

 

[Rick Brandt]

The definition of a Type A order is indeed an order that MUST contain
only Type A
parts. But how can I do that?

I'm not sure you can with RI. A constraint perhaps could do it, but I am not
very familiar with applying constraints to Jet tables. You could certainly
control it with the form used to enter the order.

The BeforeUpdate event of the details form (or of the part number control) could
test the type of the part against the type of the order and raise an error while
cancelling the update when the wrong type is entered.

Also if you use a ComboBox to enter the part it could be filtered to only show
parts of the appropriate type with LimitToList set to Yes.

 

[Allen Browne]

Okay: the business rule could be that way round.

If so, Rick's suggestion of the BeforeUpdate event procedure of the subform,
and the RowSource of the ProductID combo sound good.

 

[Jeff Boyce]

Perhaps totally out of left field, ...

Instead of constraining at the table level, another approach might be to
constrain ProductType via forms.

I can imagine setting up a main form (Order), subform (OrderDetail) design
where-in the only available ProductType on the subform is determined by the
OrderType on the main form.

That doesn't handle the table/RI issue, but does seem like it would ensure
that OrderType=A could ONLY have ProductType A.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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