If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"conditional" or "partial" referential integrity?
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... |
#2
|
|||
|
|||
"conditional" or "partial" referential integrity?
ganjula wrote:
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 Please feel free to quote anything I say here. |
#3
|
|||
|
|||
"conditional" or "partial" referential integrity?
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 Please feel free to quote anything I say here. |
#4
|
|||
|
|||
"conditional" or "partial" referential integrity?
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 "ganjula" wrote: 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 Please feel free to quote anything I say here. |
#5
|
|||
|
|||
"conditional" or "partial" referential integrity?
ganjula wrote:
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 Please feel free to quote anything I say here. |
#6
|
|||
|
|||
"conditional" or "partial" referential integrity?
"=?Utf-8?B?Z2FuanVsYQ==?=" wrote in
: 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 |
#7
|
|||
|
|||
"conditional" or "partial" referential integrity?
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 "Vincent Johns" wrote: ganjula wrote: 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 Please feel free to quote anything I say here. |
#8
|
|||
|
|||
"conditional" or "partial" referential integrity?
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? |
#9
|
|||
|
|||
"conditional" or "partial" referential integrity?
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 Please feel free to quote anything I say here. |
#10
|
|||
|
|||
"conditional" or "partial" referential integrity?
"=?Utf-8?B?Z2FuanVsYQ==?=" wrote in
: 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 a 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Referential Integrity Problems | georgeg via AccessMonster.com | General Discussion | 2 | November 2nd, 2005 05:56 PM |
Multiple referential integrity | johnver | General Discussion | 3 | July 21st, 2005 08:18 PM |
Referential Integrity | Shoelaces | General Discussion | 1 | May 12th, 2005 06:38 PM |
Edit referential integrity through SQL | JSSAggie | General Discussion | 3 | April 29th, 2005 04:29 PM |
Referential integrity in many-to-many? | Bruce | Database Design | 2 | June 2nd, 2004 03:19 PM |