I have two tables
T1 T2
C1 C1
C2 C3
The key to T1 is C1,C2.
The key to T2 is C1, C3
I need to create a third table T3 whose key will be C1, C2, C3
When I create the relationship from T1, C1 and C2 get imported
When I create the relationship from T2, C1 gets imported a second time.
How do I get C1 to only appear one time in T3?
set focus on relationship line and open the relationship properties editor window. change the columns as you see fit - i.e. have both C1’s be from T1 or T2 (your choice). This problem has a name - it’s caled unification - and I’ve blogged about it on toad world some time ago:
www.toadworld.com/…/398.data-modeling-its-really-all-about-the-relationships.aspx
www.toadworld.com/…/397.flawless-logical-to-physical-data-model-transformations.aspx
NOTE: In many cases this may seem like a good data model, but while I said and blogged how to get around it - often it’s a clue of a questionable database design. For example if you tie the T3.C1 to T1 - then what happens when T2.C1 value that does not exist but does exist in T1.C1 allows you to use your T1 based relationship to say referentially correct. In fact you might be safer keeping both columns - really, if data accuracy is your main concern …
I don’t understand…
There will be a foreign key between T3 (c1,c2) to T1 (c1,c2) and a separate foreign key from T3(c1,c3) to T2 (c1,c3)
That would mean that no combination of c1,c2,c3 could possibly be in T3 where the c1,c2 is not in t1 and c1,c3 is not in t2. That would be correct data.
This worked totally automatically in ERWIN.
If you look at my example in my blog that’s exactly what I show. You never said in original post about relationships between t1 and t3. You can unifi, it will be fine (i.e. good design) and all you need to do is to pick either t1 or t3 column in relationship editor and it will auto remove the duplicate one from other tbale. Should take 2 seconds to fix.
how did you interpret this:
"When I create the relationship from T1, C1 and C2 get imported
When I create the relationship from T2, C1 gets imported a second time. "
Guess I wasn’t clear that the “relationship” from T1 to T3 and T2 to T3, resulting in duplicate column C1.
Sorry.
No problem - as long as you now know how to fix this. We chose to import dups so user can see and then let them unifi them. If we did it other way we’d be hiding the info from user and not letting them make the best choice. ANyhow that’s the logic for why done way it is.
PS - I worked at Logic Works before Platimun bought them, before CA bought Platinum - so I know ERwin very well. I helped to design it - and I was at Embaracdero and worked on ER Studio as well. So I’ve been around on data modeling tools 
thank you, I see how to do it now. UI is a bit fidgety sometimes. For example, it says "Press F2 to change the selected child attribute. Pressing F2 does nothing at all. Clicking on the child attribute very frequently does nothing at all either. Occasionally, clicking on the child attribute will open up the drop down.
You are 100% correct. That is suppsoed to be fixed in newer versions (I think >= 5.1). So you might want to upgrade. Not only is the activation easier (one mouse click where you expect it), but the screen was redesigned to show you more info and it’s all on first/main tab - so no looking for it. If you are on maintenance (free 1st year) you can download upgrades from our support site for free. We just released 5.2.4 …
Unfortunately, I’m past the first year. Gotta get the boss to pony up. Fortunately it’s a whole lot cheaper than ERWIN was, so I just have to go and ask for it. Then the long, long, long trip through purchasing… :(. But thanks for the heads up. I’ll pursue the upgrade now.
Hi,
in version 4.x it was necessary to select the child attribute (focus had to be on the child item name) and then press F2, in that case combo box with available columns appeared in UI.
Also, it was necessary to set properties on three different tabs:
In newer versions you can define key properties on one single tab:
We improved the UI, reduced number of necessary clicks to minimum, modified UI items on key forms to simplify user flow (No F2 key is necessary, combo box is there by default), to improve overall readability and to avoid confusions.
Regards,
Vaclav
