Setting Relations Between 3 Tiered Data + Cascading Updates/Deletions 2

[drosenkranz]

Hello,

I need to set some relationships in a 3 tiered relation between several free tables. All of the records have unique integer identifiers in each of the tables. I'm not clear on exactly how to:

1) Set the relationships up and...
2) Can they be used to perform cascading updates and deletions to their respective "child" records too.

1st level is the table "PSForms" with a unique identifier "PSFormsID".

2nd level is the table HHMems with a field named "PSFormsID" to relate to the parent and another field named "HHMemsIS" which identifies each household member on the form.

The HHMems table is also the parent for the third tier of tables - the "HHIncs" (HouseHold Incomes) table and the "HHRes" (HouseHold Resources) table. Both of these tables contain a "PSFormsID" and "HHMemsID" field which relates them back to the household members table.

1) How do I set up 3 tiered table relationships?

2) Can these relationships between free tables be used to perform cascading Updates and Deletions within these relationships?

Thanks For Your Time,

Dave

The 2nd mouse gets the cheese.

 

[rgbean]

Dave,
Cascading updates and/or deletes only work "automatically" if the tables are in a database and you've set up the appropriate RI (Referential Integrity) rules.

For free tables, you'll have to write the code yourself - just like we did in the "olden" days!

Rick

 

[drosenkranz]

Hello rgbean,

Thanks for the reply. I had this project in a database with the whole ball of wax. Problem was the "wizard" generated pageframe grids kept getting corrupted and the data environments were kicking my tail up and down the block. Every structure modification affected the grids really badly - just ripped it a part and went back to free tables.

The scenario above has the HouseHold Members grid as the 2nd tier. It relates to the PSForms Table above it with the PSFormsID (integer) field/index and to the 2 tables below it by PSFormsID and HHMemsID (integer) fields/indexes.

As far as setting the relations go - is it possible to code this 3-tiered set of relations between the tables above since they use different fields and indexes?

Thanks for your time,

Dave

The 2nd mouse gets the cheese.

 

[rgbean]

Dave,
Yes you can set up the relationships as you've described in the Database Designer wndow.

However, for "cleaner" RI code, consider getting a rewritten version at Steve Sawyer's site -

http://www.stephensawyer.com/.

Click on "Technical Info and Tools" and then on "Referential Integrity Code". Note: The link to the Stonefield site is "bad", try

http://www.stonefield.com/,

then click on "Technical Papers", then download the code for "Building and Using VFP Developer Tools (148K)".

Note: As far as I can tell MS hasn't updated the RI code since a consultant wrote it for VFP 3.0, and while it works most of the time for most keys, it's got some major problems. (Readability is certainly one of the biggest!)

Rick

 

[ramani]

Hi Dave

Most often I write the RI code myself and then use it as part of my Update, Insert, Delete triggers.

These triggers.. for example.. myDeleteTrigger() is the only thing I put on the appropriate place in the DBC. The code itself is kept in a library as myTriggers and compiled with the project. The idea is that you have complete control and replacement of DBCs becomes just easy. Even the triggers library can be easily replaced based on the project under question and the reusability works well.

Well the point is that roll down your own RI code which you can understand very well and work as you desire.

Byproduct advantage is that DBC corruptions are heavily minimized and the failures are rare.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/