Relation Design

[Wayne]

Here is a database design question.

A Client Note can be added for a Work Order at any time.
This means that a Client Note can be added BEFORE the Work
Order is even received. In this case, a WorkOrderID will
not be known at the time the Client Note is added. A Work
Order record does not exist until the Work Order is
actually received and entered in they system. The
WorkOrderID is assigned in tblWorkOrder when a new record
is created.

Multiple Client Notes can be added for the same Work Order
either before or after the actual Work Order is received.

How can a Client Note record be associated to a Work Order
record if the WorkOrderID is not known? Does anyone have
design suggestions for this scenario? Should
tblClientNote and tblWorkOrder even be related?

Thanks.

 

[Jeff Boyce]

Wayne

One approach would be to associate the Client Note with the Client. If you
also add a field to the ClientNote table that can hold a WorkOrderID, when
you finally get a Work Order, you can ALSO show which Work Order a Client
Note is associated with.

Good luck

Jeff Boyce
<Access MVP>

 

[Wayne]

That's an interesting approach to think about.

Thanks Jeff.