Re: Re: Join tables in MySQL

On Dec 3, 2004, at 9:32 AM, jbs_mailuk wrote:

> David, thank you very much, that's very informative!
>
> Indeed my issue is with Scenario 2, where I have additional
> attributes.  I still want to do the equivalent of using flattened
> relationships (why the prohibition?) without doing extra work - I
> would be able to do that in a low-level SQL based system.

It is inadvisable to flatten the relationship in your model if you need 
to expose the intermediate relationships. This will give EOF two paths 
to the same data and will result in "bad things". For one it will 
definitely screw up your RI rules (I know I speak from experience).

> If I have a TermCon which has two relationships (equiv to FK) to
> Entity1 & Entity2, I want to be able to generate a list (e.g. NSArray)
> of related Entity2s to Entity1, something like Entity1.entity2s().

You can still do this:

In Entity1 create a convenience method:
	
	public NSArray entity2s() {
		return (NSArray)this.valueForKeyPath("termCons.entity2");
	}

Key Value Coding will give you the array you want.

Or if you are in a WOComponent (that has anEntity1), and you are 
binding to a WORepetition,  you can use:
	
	Entity1 anEntity1; //assume exists
	Entity2 entity2Enumerator;

WORepetitiion bindings:

	list: anEntity1.termCons.entity2
	item: entity2Enumerator

However if your TermCon has values you wish to display you will 
probably end up doing something like this:

	Entity1 anEntity1; //assume exists
	TermCon termConEnumerator;

WORepetitiion bindings:

	list: anEntity1.termCons
	item: termConEnumerator

And bind the WOStrings that will display values:

	WOString1:
		
		value: termConEnumerator.rank

	WOString2:
		
		value: termConEmumerator.entity2.name

	WOString3:
		
		value: termConEnumerator.entity2.value

I hope that is clear

> However, there is no such flattened relationship, so it looks like I
> have to
> a) use Entity1.TermCons() to fill an NSArray and then
> b) loop through this to select related TermCon.Entity2() to fill a new
> NSMutableArray, checking to ensure uniqueness.

> This results in an array of all the Entity2s related to a particular
> Entity1.

See above.

>
> This is entirely doable but it's all in code, which surely defeats the
> advantage of using WO relationships.  Hence I thought there must be
> some way of explicitly creating a 'Join Table' entity and using the
> flattened relationships, hence the desire to create rows in a dual-PK
> table, or at least the WO high-level equivalent.
>
> At the DB level, the TermCon table only needs one PK, as the 2
> entiities are mapped by 2 FKs, all eo_pk_table friendly.

There is no need for the separate PK (unless Entity1 can be related to 
Entity2 multiple times), make each FK half of a compound primary key, 
and have the relationship from their parents "propagate primary key".

> However, thinking low-level for a moment, why can't I use an explicit
> dual-PK table and have WO make sue of flattened relationships, or even
> use FK-flattening through a single-PK table?

EOF will not do FK flattening, but as I mentioned "propagate primary 
key" from the parent entities will handle assigning the compound PK 
values for you.

;david

--
David LeBer
Codebase Software Systems
site:   http://www.codebase.ca
blog: http://david.codebase.ca