HELP! Data modeling - associate (linking) table

[spfiore]

Hello, I'm new to EA and in using the data modeling side I've run into some very troubling behavior. I tried to create an associative table (mapping a many to many relationship) and EA won't allow me to create the relationships correctly. I need to have the primary key on each side related to its equivalent in the associative table, and EA seems to only allow one PK relationship at a time. Once I define the relationship on one side of the table, when I try to create the other it always uses the first key instead of the one I select.

Here is what I'm trying to do:
Tables: PersonSocialDetails...| PersonCapability | Capability
Keys:    PersonSocialDetails-> PersonSocialDetails
Keys:    ..............................Capability...........<-Capability

The middle table should have two keys that are both primary (compound key) and forign.

If this is really a shortcomming in the tool, it is a huge show stopper for us and will force us to go back to ERWin or Embarcadero, or the like. I REALLY don't want to do that, so PLEASE help me with this.

Thanks  

[Bruno.Cossi]

Hi,

actually, the middle table should not have two primary keys, that would be incorrect both logically and semantically. The middle table should have three keys:

1 PRIMARY key, that contains columns that are part of the primary key on PersonSocialDetails AND those that constitute the primary key on Capability
2 FOREIGN keys, one linking PersonCapability to PersonSocialDetails, one linking it to Capability.

I believe you can model this within EA. You can't model multiple primary keys on a table, but that is because relational databases (and SQL norms) do not allow that. That is what alternate keys are for.

Hope this helps!
Bruno

[spfiore]

Thank you for refining my question - the crux of my problem is I can't seem to be able to define a compound key (i.e. a primary key composed of the two forign keys) just as you describe. If I could define the Primary key as a compound key, then I suspect it will work.

Sooo, how do I define a compound key?!?

Again, any help with this would be MOST appreciated.  

[spfiore]

I just figured this out, its all in the sequence -

Before I created the associative table, and declared the primary keys before closing the table. I then tried to create the relationships, and couldn't do it.

The correct way is to create the associative table, create the attributes, but NOT declare a primary key (yet). Next step is to create the relationships, and THEN go back into the associative table and declare the two (now forign key) attributes as primary keys, and everything works.

This does get to a potential feature request - the ability for the relationship to create the necessary forign keys. Unfortunitely I'm in a crunch so I'll have to post that latter.

Thanks for your help. It is a HUGE relief to know I don't have to abandon EA. Thanks again.

[Bruno.Cossi]

Ah, you beat me to it! Glad it all worked out :-)
I like EA too, for database modeling purposes it does not provide all of the features that purely database modeling tools do, but ability to have both the system and database design within one model is beautiful.

As far as the relationship creating the foreign keys goes, that would be something that would belong to the logical/conceptual model in the traditional database modeling tools. EA really supports what the DB tools would call physical model, where the relationships (including FK) have to be explicitly defined. Ability to have logical model and translate it automatically into a physical one would be a very nice touch indeed!

Bruno