Many of the new frameworks (Ruby on Rails, Hibernate, JPA) recommend using a technically generated, surrogate key as the primary key for each table. The natural keys become a secondary unique key.
Unfortunately, the use of the identifying relationship doesn’t support this and Non-identifying is not really optimal either.
What is really needed is an Identifying Relationship where the parent key becomes a non-null foreign key in the child table.
Would it be possible to add support for this type of relationship.
I believe that identifying relationship where the parent key becomes not-null foreign key in child table is exactly what Toad Data Modeler offers. (Make sure you work with physical model and not logical model).
DB_Traditional is the traditional way of modeling identifying relationships.
The parent primary key is part of the primary key of the child.
DB_NonTraditional is the way that is recommended by the newer frameworks.
The child entity has its own primary key.
The parent primary key is part of a unique secondary key.
The only way to do this in TOAD now…
use a non-identifying relationship
then manually define the unique secondary key in the child…
this unique secondary key is what contains the parents primary key.
The desirable solution would be to have an identifying relationship that creates a unique secondary key (or natural key) instead of a primary key in a child.
DB_Traditional is the traditional way of modeling identifying relationships.
The parent primary key is part of the primary key of the child.
DB_NonTraditional is the way that is recommended by the newer frameworks.
The child entity has its own primary key.
The parent primary key is part of a unique secondary key.
The only way to do this in TOAD now…
use a non-identifying relationship
then manually define the unique secondary key in the child…
this unique secondary key is what contains the parents primary key.
The desirable solution would be to have an identifying relationship that creates a unique secondary key (or natural key) instead of a primary key in a child.
thank you and please accept my apologies for the delay. I am still a bit confused about the requirement to use IDENTIFYING relationships. Can you send me a piece of SQL code, please? I support that whether attribute is a part of PK or not is a matter of database an not framework.
To be honest I see no problem with both models.
Maybe excepting that nonTraditional apporach, which becomes rather incorrect than non-traditional one.
What is required by some frameworks is only shortcut. But I believe they makes possible to construct composite PKs
in any way, based on theory, you cannot have 2 identifying but separate keys. One entity may have only one primary key. period.
Finaly your implementation with two NN FKs and Unique Index over is good enough.
Really I cannot a reason why TDM should ‘hack’ such models.
I have one more idea for Spunkmeyer
PK should be indeed created as composite one, based on Foreign Keys. It will satisfy model side of view.
Then, additional id (required by framework) should be added as additional column with unique key with using of some auto-numeration method (sequence, auto increment etc).
I thing it would be the best approach. Clean from model point of view and satisfying a framework
Thanks Michal again! Issues like this we have to discuss, investigate and probe really thoroughly and carefully. But your idea or concept looks pretty good and reasonable.
Will let you know about our decision.
Thanks for your input. Unfortunately, this would involve a lot of extra work.
Every time I create a relationship, I’d have to change from the composite primary key to the unique secondary key that the framework expects.
I’m not sure I described it clear enough. In my the last reply I suggested to leave PFK untouched and add additional unique column manually to satisfy framework needs.
Still I want to notify that this approach is not correct (or at least bad practice). It requires more resources (for additional data and index) and may be confusing in further developing. So I’d suggest to make framework supporting composite keys if possible.
I was on vacations and couldn’t respond earlier.
First of all, I’d like to thank you Michal for your opinions and suggestions.
To the matter: I read the communication carefully and I think that TDM now works OK. Michal’s feedback about PKs and relationships is correct.
Also, I think it is not a good idea to display solid line for relationships that cannot be identifying. This may result if confusion.
Jay, let’s focus on what can be modeled and how to better automate the required actions. I can write set of macros that will, for example:
add new entity with new PK attribute automatically (no need to add PK attribute manually)
open a new form where you will be able to select parent table, child table and specify name for third table. The macro could create the third entity, and new unique/alternate key to it and link PK attributes to the unique/alternate key. etc.
What I need is to perfectly understand what you do. If you can, send me reverse engineered model to modeling@quest.com and provide additional information about steps you are required to do to get what you want. Ideally based on some real model and not only sample.
