OK guys,

Thanks so far for the replies.

I have thought of another scenario which may provide the solution to
problem I face.

What if I don't enforce referential integrity? That would mean I would
be modelling exactly the real life scenarios we are likely to encounter,
with a child not being recruited to the trial, whose parents details we
don't possess (for whatever reason) or who may not have either living
parents etc, etc).

In this case, we have a child table (PK ChildID, FK MotherID, FK
FatherID) a father table (PK FatherID) and a mother table (PK MotherID).
I know there's duplication going on there.

Crucially, if I didn't enforce referential integrity, this would more
closely match what happens in the trial and would allow me to create a
data entry form which exactly matches the paper CRF (case report form).
This is another thing the PI (Principal Investigator) has insisted on.

I know that there are dangers with creating orphan records here, but if
we did regular queries to check all the orphan records, and knew which
ones were meant to be orphan records from the paper CRFs, we could then
investigate those which weren't and hopefully remedy the situation.

I'd really appreciate some input from you as I'm sure they'll be a
thousand and one reasons you'll think of that I shouldn't go down this
route.

TIA

On 16/09/2011 13:57:21, Mo wrote:
Would you like a micky mouse version of what you need to play with. If so, I
will email it to you. What version of Access are you using?

Is your email address as shown in the NG?

Phil

Mo wrote:
How is enforcing referential integrity preventing you from performing your
task?

Enforcing referential integrity will not prevent this.

And that indeed is the problem. This design locks you into this model. You
will never be able to use it for anything else. You might be thinking
"that's not a problem", but failure to normalize prevents you from using
some very basic features of the SQL language. Think about the hoops you will
need to jump through to answer a simple question like, "How many parents
have been entered for each child?"
With the normalized structure we recommended, it's a simple grouping query:

select childid, count(*)
from ChildParent
group by childid

Sure you can probably come up with a query to answer that question with your
proposed design, but it's not going to be anywhere near as simple as the
grouping query.

with no parents. What is prevented is adding a parent that has no person
record. You gain nothing by failing to enforce referential integrity.

I think what we have failed to point out is the need to provide a form to
enter a person record for a parent. In the subform, there should be a "New
Parent" button that launches a modal ParentEntry form to enable the addition
of a Person record for the parent. When this form is closed, the subform
needs to be refreshed to allow the combo box to display the newly entered
Person.


Recipe for disaster. Believe me.

the paper form.

Hello Phil,

My email address is visible if you go to the 'source view' of one my
messages.

If you can't find it, just shout.

Many thanks.

Bob Barrows wrote:
record is automatically entered in the combo box.

On 16/09/2011 15:48, Bob Barrows wrote:
Yes, I begin to see how your model would work Bob.

Mo