Toad World® Forums

Better support for new Frameworks

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.

Thanks,
Jay

Hi Jay,

can you write me more information, please?

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).

BTW: there is a macro that can be used for reversed Ruby on Rails database structures.
http://modeling.inside.quest.com/entry.jspa?externalID=4492&categoryID=34

Please send me a piece of SQL code and I will try to show you how the model would look. Thank you.

Regards,

Vaclav

Sure…Hope this helps. I’m posting two pictures.

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.

Let me know if you need more information.

Thanks again,
Jay
DM_NonTraditional.png

Sure…Hope this helps. I’m posting two pictures.

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.

Let me know if you need more information.

Thanks again,
Jay
DM_Traditional.png

Hi,

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.

Thank you,

Vaclav

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.

best regards

Thanks Michal for your opinion.

Cheers,
Lukas

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

best regards

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.

Regards,
Lukas

Hi Michael,

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.

Best Regards,
Jay

Hi Vaclav,

Thanks for the response.

I can’t really demonstrate this with SQL. The SQL from TDM is fine.

This is about a difference of workflow and presentation.

If you look at my examples, the intermediate table is fully dependent upon the other tables.

In the second diagram, I’m forced by the framework to use a surrogate primary key (even though it is still fully dependent).

I think it would be a nice feature for TDM to have an option, such as “Every entity has a surrogate primary key.”

Then, when I create relationships, I could use the identifying relationship so I get a solid line.

Hope this helps. Of course I understand that this is just a convenience feature. But still it would save time.

Thanks again,
Jay

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.

best regards

Hi Jay and Michal,

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.

Some actions can be nicely automated. Please read this article:
http://blogs.inside.quest.com/modeling/2011/06/14/how-to-use-productivity-pack/

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.

Again, thanks for you feedback and suggestions.

Vaclav

Message was edited by: vaclav

Thanks Vaclav.

I’ve been away too. Sorry for the delayed reply.

I’ve become pretty proficient with Macros. I should be able to do these. I’ll let you know if I have any questions.

Thanks,
Jay