Toad World® Forums

logical-to-physical conversion and parent/grandparent keys


#1

Hello,

I am new to formal data modeling and TDM. Excuse me if I am asking a simple question.

I create a Logical model with a few entities, with simple one-attribute primary indices. And I declare some of them to have one-to-many relationships.

Where there is a hierarchy of relationships, I get a behavior I am surprised by when trying a Logical-to-Physical conversion (I chose SQLServer 2005).

Physical tables created as a child in a relationship hierarchy are given not only the parent entity’s primary key as a column, but also the parent’s parent’s primary key (and parent’s grandparent, if applicable, etc.)

Is this a natural result of formal data modeling?
Or is it a part of this tool?

Can I influence this behavior in the tool?

Thanks!


#2

Hello,

It depends on what relationship type you have between the entities.

Identifying relationship - PFK attributes are created in PER model.
Non-identifying relationship - FK attributes are created in PER model.

Please find attached a doc with some examples on how it works in TDM.

In any case, please have a look at the Help file, Index tab and type Foreign keys. See other related topics - Relationship Types, LER to PER conversion etc.

If you have any questions, please write me back.
Thanks.

Regards,

Vladka
MigrationOfKeys.pdf (199 KB)


#3

Thanks for your message.

I do see how the Identifying vs Non-Identifying relationship impacts the conversion.

I think I also see the logic of what happens–

 I believe that for each relationship, the Primary Key field or fields of the Left side are copied into the Right.
 Then, if the relationship is Identifying, those Foreign Key fields in the Right side are combined (along with any Unique Identifier defined in the LER) to form the Primary Key of the Right side entity.
 When this is repeated through a chain of relationships, the "key migration" effect is seen.

 This makes sense in terms of the "Identifying" concept.

 But it isn't how I prefer to implement the Physical tables.

 My habit is to have an incrementing integer column in every table which is used for the foreign key in other tables.

  Since this is a pretty common practice, is there a way to get the tool to implement this strategy?

#4

If we’ve misunderstood your question or if you have more questions, please write us back. Thanks.

Regards,

Vladka + TDM Team

Hello,

In Physical model, you can create such a column and connect it via non-identifying relationship with child table.

Nevertheless, possibly you’d like to set a column autoincrement in LER model before the conversion. Do you mean this?
If so, I’m afraid, this is not possible. Autoincrement is a physical issue. Also, autoincrement is supported only in some databases.


#5

Thanks again for your reply.

You have understood much of my question, but let me restate it to be

specific.

The exact option I was asking about would be an option in LER->PER that

results in the following:

    -- in creating relationships, for each "Left" side table, create an

abstract, single-column unique identifier, preferably an integer with a
unique constraint (and how about autoincrement if supported by the target)

    -- then for each "Right" side table, use that new, single-column key

as the Foreign Key

It sounds like we don't have such a behavior in TDM.

If not, is there possibly a way we can script it as users?

#6

Hello,

Thanks for the details.

Current solution in TDM on what you require is:
A. LER Model

  1. Create Entity1 with attribute Integer (in parent entity).
  2. To have a unique constraint in PER model, create another unique identifier where you put the attribute. Edit the Entity1 | Unique Identifier tab, add UI2, edit it and assign the attribute to it.
  3. Create Entity2.
  4. Create a relationship between the entities.
  5. Edit the relationship | General tab | from the Foreign Unique Identifier box, select the UI2.
    (It is shown in this flash movie:
    http://modeling.inside.quest.com/entry.jspa?externalID=2452&categoryID=158
    Part I)
  6. Do the conversion to PER model.

B. PER Model (SQL Server)
Edit the attribute, and in its Properties dialog | Identity tab | select the Identity box.

Regarding using the scripting in this example, it could help you to do the following:

  • create an attribute,
  • assign data type Integer,
  • select the Identity checkbox.

Only these things can be done via scripting (if you have a large model), other things must be done manually.

If you have any questions, please write us back. Thanks.

Regards,

Vladka + TDM Team


#7

Ok,

But then how does this address the original “key migration” issue?

I understand your description of how to create a relationship and specify the parent key.

But if I also have “Identifying” relationships, then I will get the integer and the automatic keys from the Identifying relationships, right?

What I am talking about is a complete solution to using a proper Logical model, including Identifying relationships where correct, and improving the implementation of foreign keys in the LER->PER conversion.

I realize that I could specify all “Non-Identifying” relationships in the Logical model, and create all my integer row identifiers for keys in the Logical model, and then do a conversion.
But if I can’t get a usable Physical model from a correct Logical model, which can include Identifying relationships, then how are we getting value out of the Logical model (which is one of the biggest features of TDM) ?


#8

It seems to me from reading this (and I may be wrong) - that there is confusion about identifying versus non-identifying relationships in data modeling

Identifying: Parent Attribute = PK, Child Attribute = PK and FK both

Non-Identifying: parent Attribute = PK, Child Attribute = FK Only, the entity has it’s own PK in another column

PK’s Attributes need to have SQL Server Auto-Increment set, and Oracle PK’s need a sequence # generator tied via trigger

But FK’s don’t need anything at all - they simply are NULL or point to existing rows in the parent ???


#9

Thanks for your message.

At this point, I think I understand what is going on with the relationships but I would prefer a different implementation for LER->PER.
And the answer is there is no such implementation.
But that’s ok.

I might start a new thread in the “Enhancement Request” forum at some point.


#10

hi

im also new to this, but from my very limited understanding, there is no concept of PKs and FKs at the logical level.

i think it would be a great feature if one could define all attributes in all entities as usual in a logical model, but upon conversion TDM recognise identically named attributes mapping from one entity to another and automatically designate FKs in child tables, perhaps presenting a confirmation list so users could weed out any anomalies.

my modus operandi at the moment is to define all attributes in all entities in the logical model, even though after conversion the physical i will have duplicate named keys in the child tables. my plan is to delete the duplicated keys manually.

if one were omit attributes in logical entities, with the forethought that the conversion process will produce the relevant entries automatically, i do not see how the logical model could be a correct representation of the logical system one is modeling.

anyhow, i am very very green to all this and i am ad-libbing the modeling process, so perhaps i simply do not understand data modeling or the process thereof.


#11

Hello,

Foreign keys are not displayed in Logical Model. Logical model shows a model only from the logical point of view. Foreign keys are a thing of physical modeling.
There are significant differences between LER and PER models, e.g. inheritances in LER model when migration of keys simply cannot be displayed.

Please read e.g. discussion at:
http://modeling.inside.quest.com/thread.jspa?messageID=11862

my modus operandi at the moment is to define all attributes in all entities in the logical model, even though after conversion the physical i will have duplicate named keys in the child tables. my plan is to delete the duplicated keys manually.

We do not think this is a good idea.
You don’t have to do this, TDM will do this job for you - after the conversion the FKs will be created automatically in child entities. You write you will delete them manually. - It is not possible, you can only map the foreign keys, which is not a one-shot task for all the tables… Also, there are three ways how to convert inheritance from LER to PER model.

References:
Help file - Index tab | inheritance - convert.
Movies on conversion:
http://modeling.inside.quest.com/entry.jspa?externalID=1682&categoryID=158
http://modeling.inside.quest.com/entry.jspa?externalID=2064&categoryID=158
FK mapping:
http://modeling.inside.quest.com/entry.jspa?externalID=1668&categoryID=158

If you have any questions, please write me back.

I hope you find the information helpful.

Regards,

Vladka