Wiki Home

Set Relation


Namespace: WIN_COM_API
An alternate understanding...
I am not a big fan of the terms target and source. I think it is clearer to understand parent and child in these terms:
When defining the relationship of parent and child, all records of the parent cursor are relevant. In the child cursor, only those rows in the child cursor that match the parent records are relevant. In other words, only the child cursor can possibly have records that have no meaning in the relationship or even no records in the relationship.

In code it looks like this...
use child_table alias child_cursor order key_expression_tag in 0
Select 0
use parent_table alias parent_cursor
set relation to key_expression into child_cursor


Ben Creighton


In the context of an "implemented relation" (or some better term) (the result of using the xBase set relation command):
The parent is the source of a relationship. In a persistent relationship it is usually the table that a foreign key points to, but it can be the other table in a relation created with the SET RELATION command.

The child is the target of a relationship. It is usually the table with the foreign key in a persistent relationship in a database, but it can be the other table in a SET RELATION relationship. The correct useage of these terms in relational theory is Parent is the source of a relation and the child is the target of a relation.

For years, the xBase community referred to "tables" as "databases". Then when VFP came out with programmatic support of a database (rules, triggers, RI), it made documentation and conversations real confusing. I think a similar problem exists with the parent/child terms, so I am starting a revolution.

Using database to refer to a table is a misuse of terminology, but the definition of parent and child in a relationship is exactly as the help file describes it. The role of parent or child can and does change.

I think the root of this is in the description of the command SET RELATION

The "set relation" command can set a relation either from a parent into a child, or from a child into a parent; but the documentation suggests that the parent is defined as the "from" table and the child is the "into" table. Here are some examples copied form the help:

The SET RELATION description is exactly correct. That is what parent and child mean, parent is the source of a relation and child is the target of a relation.

The relational expression is usually the index expression of the controlling index of the child table.

... the child table must be indexed on the common field.
INTO nWorkArea1 | cTableAlias1
Specifies the work area number (nWorkArea1) or table alias (cTableAlias1) of the child table.
.. include the IN clause to specify the parent table's work area

Lets say we want to see orders, who ordered and what the current status of the order is, sorted by order date. (Remember, this is an example of the "set relation" command, so using SQL is outside the scope of this discussion.)

modify database Test4 nowait
create table Client;
  ( cClKey c(10) not null primary key, ;
  cClId c(10) not null unique, ;
  cClNam c(20) )

create table Orders;
  ( cOdKey c(10) not null primary key, ;
  cOdClKey c(10) references Client, ;
  cOdId c(10) not null unique, ;
  cOdDsc c(40), ;
  dOdPlaced d , ;
  yOdPrice y  )
index on dOdPlaced tag dOdPlaced

create table OrderStatusLog;
  ( cOlKey c(10) not null primary key, ;
  cOlOdKey c(10) references Orders, ;
  cOlStt c(1), ;
  tOlProcessed t  )
index on cOlKey + ttoc( tOlProcessed, 1 ) descending  tag KeyProc

use Client in 0 order cClKey
use Orders in 0 order dOdPlaced
use OrderStatusLog in 0 order KeyProc
select Orders
set relation to cOdClKey into Clients, cOdKey into OrderStatusLog

set


You get this neat little dialog that shows Orders at the top of a tree, with Clients and OrderStatusLog connected beneath. If you hit the "1 to many" button, you get a dialog that states "Parent Alias is Orders", and "Child aliases: Orderstatuslog, Clients".



Question: When did the Clients table become a child of Orders? Answer: When we started using the terms "Parent" and "Child" in reference to the primary and secondary tables of a relation (using the old dBaseII terms).

Clients became a child as soon as you made it the target of the relationship. Using parent and child to reference the controlling and controlled alias is the correct use of those terms.

I think this is an unfortunate mistake, because all too often it is difficult to understand the meaning of these terms. Even when technically they are being used correctly, sometimes there isn't enough context to determine which meaning is meant. Other times I think they are used wrong.

Yes, sometimes they are used incorrectly, when someone expects that the parent and child roles of a persistent relationship in a databse are constant even though a temporal relationship reverses them. Parent and child are terms used to describe a relationship, the parent is always the source and the child is always the target of the relationship. A pair of tables can be related in more than one way, the persistent Parent Child roles in the database do not force any specific Parent Child relationship in the data environment at runtime.


Are you suggesting that if no relation is set, the terms Parent and Child do not apply? I would think that: if in order for records in table A to be meaningful, they require data from a record in table B, A is considered the Child, and B is considered the Parent. (They could both be the same table, in which case it is considered both Parent and Child.) This designation is above the level of implementation, and exists because of the requirement of the link, not how the link is implemented. The persistent relation in the dbc is nothing more than an acknowledgement that it exists; the triggers are what enforce it.

I can also see how the terms can be applied to the tables engaged in a relation, but I really think that some convention needs to be adopted to prevent statements like: a parent table is the child table when you relate the child into the parent. Yes, you can perhaps infer the meanings, but in that case you wouldn't really need the different terms.

My suggestion: when referring to "set relation" relations, use "parent of the relation", and when referring to a table that holds necessary information, just "parent". This is slanted towards my view of the terms, so please help with a more generally acceptable solution.

It seems that "Parent" and "Child" shouldn't be used to describe the SET RELATION command at all, if it is agreed that these terms describe the static definition of an RDBMS structure. That is, issuing the SET RELATION command does NOT change the structure of the database. However, every SET RELATION command has one Source for the relation, and One or More Targets for the relationship. The Source table generates the events (moving the record pointer in the source table) that affect the relationship, and the Target table(s) are affected by the relationship (their record pointers automatically move). The word "Target" is already frequently used when referring to SET RELATION, so why not make it an official pair? - wgcs

Seems that if you don't access the target of a relation, VFP doesn't bother taking the time to find the related record.

Seems using SEEK is faster.

Close All
Clear All
Set Talk Off

Clear

lnLoopSize = 500
mkdb()

? "Process( tlSetRela, tlAccessPnt, tlGroup )"

For lnFlags = 0 to 2^3-1
	Process( Bittest(lnFlags,2), Bittest(lnFlags,1), Bittest(lnFlags,0) )
EndFor

best()

Return

Function Process( tlSetRela, tlAccessPnt, tlGroup )

? tlSetRela, tlAccessPnt, tlGroup

ltStart = Seconds()

USE Pnt In 0 Order kPnt_pk
USE Cld In 0
Select Cld
If tlGroup
	Set Order to kPnt_fk
Else
	Set Order to nItemNo
EndIf

If tlSetRela
	Set Relation to kPnt_fk into Pnt
endif
Scan
	If tlAccessPnt
		x = Pnt.cPntID
	EndIf
	y = Cld.cChldId
EndScan
?? Seconds() - ltStart

USE in Pnt
USE in Cld

return


Function best()

? "best"

ltStart = Seconds()

USE Pnt In 0 Order kPnt_pk
USE Cld In 0 order kPnt_fk

Select Cld
Locate
Scan while !Eof()
	m.kPnt = kPnt_fk
	Select Pnt
	Seek m.kPnt
	x = Pnt.cPntID
	Select Cld
	Scan while m.kPnt = kPnt_fk
		y = Cld.cChldId
	EndScan
EndScan

?? Seconds() - ltStart

USE in Pnt
USE in Cld

return



Function mkdb

Create Table Pnt ( kPnt_pk i, cPntID c(10) )
Create Table Cld ( kChld_pk i, kPnt_fk i, cChldID c(10), nItemNo i )

kChld = 0
For lnI = 1 to lnLoopSize
	Insert into Pnt values ( lnI, Transform( lnI ) )
	For lnJ = 1 to lnLoopSize
		kChld = kChld + 1
		Insert into Cld values ( kChld, lnI, Transform( kChld ), lnJ )
	EndFor
EndFor

Select Pnt
Index on kPnt_pk tag kPnt_pk

Select Cld
Index on nItemNo tag nItemNo
Index on kPnt_fk tag kPnt_fk

USE in Pnt
USE in Cld
return

Contributors: Carl Karsten Jim BoothOffsite link to http://www.jamesbooth.com
wgcs
( Topic last updated: 2007.05.19 03:11:29 PM )