"conditional" or "partial" referential integrity?

[ganjula]

I think I've run myself up a blind alley... hope someone can redirect
me in a more productive direction...

I have an existing system for tracking real estate Acquisitions - an
Acquisition
involves a set of steps and information for acquiring a particular Tract
within
a particular Town. Hence, there are tables like these (BOLD ITEMS ARE KEY
FIELDS):

Tracts: TOWN, TRACTNUM, Address, Description...
Towns: TOWNID, Name, State...
Acquisitions: TOWN, TRACTNUM, Milestone, Agent, Cost...

I am trying to add the ability to track Survey work. Sometimes a Survey is
specific to a particular Tract within a particular Town; other times it might
be for a group of Tracts, or for a Town in general, or for no Town at all.

In the case of a Survey for a particular Town and TractNum (the most common
case),
things can be very simple:

Surveys: SURVEYID, Town, TractNum, Surveyor, Acres...

The obvious thing is to join Surveys to Tracts using Town+TractNum and
enforce
referential integrity (RI)... this works just fine for Town+TractNum Surveys.

It also works fine for non-Town, non-TractNum Surveys... I can leave both
Town
and TractNum as Null in the Surveys table and all is well.

But, this scheme does NOT allow me to have Town-only Survey work, where I
specify
a value for the Town in Surveys, but leave the TractNum as Null. With RI
enforced,
Access imposes an all-or-nothing rule on the Town and TractNum fields in
Surveys:
either both must be Null, or together they must reference a valid record in
Tracts.

So how does one deal with a situation like this, where I want RI enforced, but
only some of the time, or only partially? Conceptually, I'd like something
like this:

SurveyType Town? TractNum? RI?
Acquisition yes yes Yes (full - Town + TractNum)
Town-level yes no Yes (partial - Town only), or No
Other no no No

I don't believe there is such a thing as "conditional RI", which makes me
think
I've blinded myself to some other way of looking at this problem and finding
the right solution.

Thanks in advance for any ideas, pointers, whacks on the head...

 

[Vincent Johns]

I think I've run myself up a blind alley... hope someone can redirect
me in a more productive direction...

I have an existing system for tracking real estate Acquisitions - an
Acquisition
involves a set of steps and information for acquiring a particular Tract
within
a particular Town. Hence, there are tables like these (BOLD ITEMS ARE KEY
FIELDS):

Tracts: TOWN, TRACTNUM, Address, Description...
Towns: TOWNID, Name, State...
Acquisitions: TOWN, TRACTNUM, Milestone, Agent, Cost...

I am trying to add the ability to track Survey work. Sometimes a Survey is
specific to a particular Tract within a particular Town; other times it might
be for a group of Tracts, or for a Town in general, or for no Town at all.

In the case of a Survey for a particular Town and TractNum (the most common
case),
things can be very simple:

Surveys: SURVEYID, Town, TractNum, Surveyor, Acres...

The obvious thing is to join Surveys to Tracts using Town+TractNum and
enforce
referential integrity (RI)... this works just fine for Town+TractNum Surveys.

It also works fine for non-Town, non-TractNum Surveys... I can leave both
Town
and TractNum as Null in the Surveys table and all is well.

But, this scheme does NOT allow me to have Town-only Survey work, where I
specify
a value for the Town in Surveys, but leave the TractNum as Null. With RI
enforced,
Access imposes an all-or-nothing rule on the Town and TractNum fields in
Surveys:
either both must be Null, or together they must reference a valid record in
Tracts.

So how does one deal with a situation like this, where I want RI enforced, but
only some of the time, or only partially? Conceptually, I'd like something
like this:

SurveyType Town? TractNum? RI?
Acquisition yes yes Yes (full - Town + TractNum)
Town-level yes no Yes (partial - Town only), or No
Other no no No

I don't believe there is such a thing as "conditional RI", which makes me
think
I've blinded myself to some other way of looking at this problem and finding
the right solution.

Thanks in advance for any ideas, pointers, whacks on the head...

Depending on the data you must deal with, you might discover that there
is a town you've never heard of that needs to be in your Table. It's
called "<unspecified>". Also, there is probably a tract with a similar
name in every town in your list that needs to be included in your Table.
Having added those, if your design allows it, I think you can link any
otherwise unattached records to the "<unspecified>" town or tract.

If the [TractNum] is constrained to be a number, then a value like
(-666) could be used for this purpose.

If referential integrity is giving you fits, it's also possible in some
cases to allow inconsistent updates of your Tables (check the Help files
for details).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[ganjula]

Vincent,

Thanks for the quick reply! This looks very promising, and not unlike
something I've implemented elsewhere in this project (for Owners and
Contacts for each Tract). I think there's a way to make it work for this
case, too. Will check it out now and post results...

Chris

 

[ganjula]

Ok... having played around with this, it's not my first choice. I have a
large-ish number of towns, and growing, and the prospect of populating
the db with a bunch of dummy records (1 "unspecified" tract for each
town), and maintaining that as new towns are added... well, it seems
like a lot of overhead.

I'm leaning instead towards a different approach now: add another
"town-only" field to tblSurveys, and either populate *it* (for non-Tract-
related surveys), or the original Town+TractNum fields for Tract-related
Surveys, or populate none of them for other non-tract, non-town surveys.
This only requires:
* adding the (slightly redundant) extra field
* deciding at the moment a new Survey record is created whether to
populate the Town-only field or the Town+TractNum pair, based on
whether the user specified one or both (or none). This entails a small
amount of code, but the whole solution feels more localised and explicit,
relative to having a bunch of dummy data to maintain.

If you have any reactions to this, I'd love to hear them. Otherwise,
I still very much appreciate the input - it did the trick for breaking
me out of a rut and getting me unstuck.

Cheers,

Chris

Vincent,

Thanks for the quick reply! This looks very promising, and not unlike
something I've implemented elsewhere in this project (for Owners and
Contacts for each Tract). I think there's a way to make it work for this
case, too. Will check it out now and post results...

Chris

Depending on the data you must deal with, you might discover that there
is a town you've never heard of that needs to be in your Table. It's
called "<unspecified>". Also, there is probably a tract with a similar
name in every town in your list that needs to be included in your Table.
Having added those, if your design allows it, I think you can link any
otherwise unattached records to the "<unspecified>" town or tract.

If the [TractNum] is constrained to be a number, then a value like
(-666) could be used for this purpose.

If referential integrity is giving you fits, it's also possible in some
cases to allow inconsistent updates of your Tables (check the Help files
for details).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Ok... having played around with this, it's not my first choice. I have a
large-ish number of towns, and growing, and the prospect of populating
the db with a bunch of dummy records (1 "unspecified" tract for each
town), and maintaining that as new towns are added... well, it seems
like a lot of overhead.

Well, if I were doing that, I would add them only as needed.

I'm leaning instead towards a different approach now: add another
"town-only" field to tblSurveys, and either populate *it* (for non-Tract-
related surveys), or the original Town+TractNum fields for Tract-related
Surveys, or populate none of them for other non-tract, non-town surveys.

Caution: Although Access doesn't care, adding fields that contain
essentially the same types of information may make it difficult to
maintain your database. (I'm not sure you're doing that, but it kind of
looked that way.) For example, you might be tempted to include in one
record two or three fields specifying the areas of tracts. But if you
did that, and later needed to determine the largest one in your Table,
you'd have to examine all those fields to find it. A cleaner design
would be to break out the similar fields into a separate Table that you
link to the original Table via a key value.

This only requires:
* adding the (slightly redundant) extra field
* deciding at the moment a new Survey record is created whether to
populate the Town-only field or the Town+TractNum pair, based on
whether the user specified one or both (or none). This entails a small
amount of code, but the whole solution feels more localised and explicit,
relative to having a bunch of dummy data to maintain.

Well, maybe "some" dummy data (the empty tract name)... I don't see "a
bunch" here. But you know your data better than I do.

If you have any reactions to this, I'd love to hear them. Otherwise,
I still very much appreciate the input - it did the trick for breaking
me out of a rut and getting me unstuck.

Cheers,

Chris

Congratulations for de-sticking yourself, and good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

I am trying to add the ability to track Survey work. Sometimes a
Survey is specific to a particular Tract within a particular Town;
other times it might be for a group of Tracts, or for a Town in
general, or for no Town at all.

This is a straighforward many-to-many relationship (strictly a zero-or-
many to zero-or-many) between Surveys and Tracts... you need a new table
called AppliesTo with fields like

*SurveyID, *Town, *Tractnum, IsPrimary, HasBeenNotified, etc etc.

The relationship with Towns is more problematical. It is tempting to
suggest a sub-typing approach with a system like

ThingsThatCanBeSurveyed (
*ThingID,
OtherCommonStuff,
etc
)

Towns (
*ThingID references ThingsThatCanBeSurveyed,
OtherTownStuff,
etc
)

Tracts (
*ThingID references ThingsThatCanBeSurveyed,
Town references Towns, // not Things...
OtherTractStuff,
OtherAcquisitionStuff, // PS Why have two tables with the same key?
etc
)


and then of course

SurveyAppliesTo (
*SurveyID references Surveys,
*ThingID references ThingsThatCanBeSurveyed,
OtherApplicabilityStuff,
etc
)


This may cause a little bit of overhead for your User Interface, but as
far as I can tell, it should remain safe and provide a complete basis for
queries.

Hope it helps


Tim F

 

[ganjula]

Thanks again, Vincent.

On further reflection, I think your suggestions make more sense than mine.
It is pretty easy to create the "unspecified" Tract for each Town, as needed,
and these will never get in the way of the Acquisition records, which cannot
point to "unspecified" Tracts. By doing it on the fly, it requires no pre-
loading of the "unspecified" Tracts, and is self-maintaining as new Towns
are added.

I've read Tim Ferguson's comments further below as well, and will
reply to them next - although they helped to clarify my thinking on
the issue, I don't think they provide the best way forward for my
particular situation, as I try to explain below.

Thanks again,

Chris

 

[ganjula]

Hi Tim,

Many thanks for your helpful response. Here comes a long reply...

I probably expressed myself poorly in my first post, no doubt due
to the brain-freeze I developed over this problem!

Elsewhere in the system, I have a true many:many relationship -
Tracts have Owners, and the following combinations are possible:

1 Tract has 1 or many Owners
1 Owner owns zero, 1, or many Tracts

I implement this with a standard linking table, similar to your
AppliesTo suggestion (if I've understood you correctly):

Tracts (
*TownID
*TractNumber
Other Tract Info...
)

Owners (
*OwnerID
Other Owner Info...
)

Tract_Owners (
*TownID
*TractNum
*OwnerID
Other Tract-owner info...
)

But I don't think the situation for Tracts, Towns, and Surveys is
many:many, as I think you were suggesting. The following are the
possible relationships among these three entities:

1 Tract is in 1 Town
1 Tract is in zero, 1, or many Surveys
1 Survey is for zero or 1 Tracts
1 Survey is for 1 Town*
1 Town has many Tracts
1 Town has zero, 1, or many Surveys

* I was wrong in an earlier message to say that a Survey could be
independent of any Town - every Survey is associated with 1 Town.

So there are no many:many relationships involving Tracts. Instead,
we have:

Towns:Tracts is 1:many
Surveys:Tracts is 1:zero-or-one
Towns:Surveys is many:many

I've played around with possible linking or "applies to" tables,
but I can't come up with a scheme that captures simultaneously
the three relationships among the three tables. At the risk of
totally embarassing myself, I'll say that I don't quite follow
your scheme. At first I thought I did - it looked like a variation
of a simple linking table with an extra level of linking or
indirection. But when I sketch it out, I don't see how it works.

If the ThingID fields in your Towns, Tracts, and SurveyAppliesTo
tables point to records within ThingsThatCanBeSurveyed, then I
infer that ThingsThatCanBeSurveyed must be getting a new record
(and ThingID) for each new Survey... otherwise, there would be
no way to distinguish (for example) one Town Survey from another.
But then, how is that any different from simply having a linking
table like SurveyAppliesTo... Either way (with or without a
ThingsThatCanBeSurvyed table), I don't see how your scheme lets
me have more than one Survey for a Town.

I think there are other problems lurking in there, as well... all
of them, in my (addled) mind seem to stem from one or both of:
* the many:many relationship of Towns and Surveys
* the independent relation b/n Tracts and Towns

This is a long post, and perhaps difficult to make sense of, but
I'm eager for any further insight/input you might have. I very
much appreciate your assistance, and apologize if I'm being too
slow to get it.

Chris

PS - in answer to your PS question: Acquisitions and Tracts have
the same key fields because Tracts can exist without a corresponding
Acquisition record (e.g., for Survey work)... it seems excessive to
carry along the 100+ Acquisition fields in the Tracts records for
Tracts that will never use them. So, yes, while they use the same
key fields (because a Tract will have at most a single Acquisition
and therefore a 1:1 relation with Acquisitions), they fill different
roles... does this seem a reasonable basis for splitting them into
separate tables?

 

[Vincent Johns]

ganjula wrote:

[...]

So there are no many:many relationships involving Tracts. Instead,
we have:

Towns:Tracts is 1:many
Surveys:Tracts is 1:zero-or-one
Towns:Surveys is many:many

But you just now said that "every Survey is associated with 1 Town",
which I took to mean "... with at most 1 Town". If so, why the
"many:many" relationship between Towns and Surveys??? You could set
your database up that way, but IMHO it would be unnecessarily complex,
as you'd never need to attach 3 Towns to 1 Survey.

[...]

PS - in answer to your PS question: Acquisitions and Tracts have
the same key fields because Tracts can exist without a corresponding
Acquisition record (e.g., for Survey work)... it seems excessive to
carry along the 100+ Acquisition fields in the Tracts records for
Tracts that will never use them. So, yes, while they use the same
key fields (because a Tract will have at most a single Acquisition
and therefore a 1:1 relation with Acquisitions), they fill different
roles... does this seem a reasonable basis for splitting them into
separate tables?

I think it does, and for the reasons you mention.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

1 Tract is in 1 Town
1 Tract is in zero, 1, or many Surveys
1 Survey is for zero or 1 Tracts
1 Survey is for 1 Town*
1 Town has many Tracts
1 Town has zero, 1, or many Surveys

* I was wrong in an earlier message to say that a Survey could be
independent of any Town - every Survey is associated with 1 Town.

That makes A LOT of difference... <g>. From this statement I think I
understand that when a Survey involves several Tracts, all those Tracts
have to belong to the same Town. If that is true, then you can put a
simple constraint on the SurveyAppliesTo table -- having said that, I am
hoping it's not too complex for Access. And yes, you can drop all the
subtyping stuff: you just need

Towns --< Tracts --< SurveyAppliesTo >-- Surveys

With that constraint, it's easy to find out which Town a Survey belongs
to because it's there in the Tracts table. You don't need an extra field
holding it anywhere; in fact to have one is a really bad idea, becuase
sooner or later you'll have Survey.Town="Aldwich" and AppliesTo.Tract->
Tract.Town="Barham".

So there are no many:many relationships involving Tracts.

Yes there are: that's exactly what you said up there^^

[...]

If the ThingID fields in your Towns, Tracts, and SurveyAppliesTo
tables point to records within ThingsThatCanBeSurveyed,

(Actually, as above, the whole subtyping argument is moot but yes...

infer that ThingsThatCanBeSurveyed must be getting a new record
(and ThingID) for each new Survey...

No: there is one TTCBS (sic) for each Town record and one for each Tract
record. There could be lots of SurveyAppliesto records pointing to each
TTCBS.

no way to distinguish (for example) one Town Survey from another.

Every Survey has its own identifier, Shirley?

I don't see how your scheme lets
me have more than one Survey for a Town.

Every Survey has (zero or) many SurveyAppliesTo records; each
SurveyAppliesTo applies to exactly one Town_or_Tract.

I think there are other problems lurking in there, as well... all
of them, in my (addled) mind seem to stem from one or both of:
* the many:many relationship of Towns and Surveys

You just said a Survey applies to zero or one Towns...

* the independent relation b/n Tracts and Towns

Independent of what? I assumed a FK field in the Tracts table that
references the Town table: "1 Tract is in 1 Town" you said up there^^

PS - in answer to your PS question: Acquisitions and Tracts have
the same key fields because Tracts can exist without a corresponding
Acquisition record (e.g., for Survey work)...

Kind of makes sense. I am always suspicious of one-to-one relationships,
but there are genuine reasons for them.

does this seem a reasonable basis for splitting them into
separate tables?

Yes. Arguable for a purist.

PS: I just made my first posting in comp.databases.theory the other day
<vbg>


All the best

Tim F