Toad World® Forums

Constructing Unique Identifiers in Logical Model


#1

I have a problem with constructing Unique Identifiers in the Logical model.I want to have a Foreign Key (Appropriate attribute from the Parent Table) as a part of the Primary Key (Unique Identifier) in a Child Entity. How can I achieve that? Can somebody give be a hint?


#2

It appears that the primary key (UID) from the parent entity does not automatically migrate to the child entity as a foreign key as it does when working in the physical model. However, when you convert the logical model to a physical model, the foreign keys are migrated. I won’t say whether this is right or wrong, but that is how it appears to works. Okay, I will say: it doesn’t seem right to me. I think the PK/FK migration should happen whether you are in a logical model or a physical model. Perhaps there is an option to turn that on that I haven’t seen so far.

See attachment.

I would suggest that you don’t artifically introduce the attributes for foreign keys in you child table. That will result in extra fields in your physical model when you convert.
logical_model_fk.doc (106 KB)


#3

Hello,

Bruce is right. In Logical model, keys do not automatically migrate from parent to child entity (unlike PER model). Nevertheless, if you convert your LER model to PER model, the keys will be displayed in child entities. - This is how it works in TDM 3.

Explanation: There are significant differences between LER and PER models, e.g. M:N relationship and inheritances in LER model when migration of keys cannot be displayed.

Regards,

Vladka


#4

I do not see any acceptable workaround of the problem.

Thank you Bruce,Yes, it works as described, but this does not solve my problem. Probably using this tool I will not find a solution for my problem.I want to see this migration in the logical model. Otherwise I’m not able to include the migrated attribute in the PK/UID of the child table. It is very common case to have Parents UID as a part of the child UID. Without that I must introduce special new attribute in the child table as UID. That as an end effect means additional logic to support this artificial UID. Additional problem is, that the Physical model does not allow me to include migrated columns (FK) in the PK. Check box “Unique” is greyed.

Mea culpa:

Should not blame system that does not know in details. After I have seen the generated script, then I have understood that notation PFK actually means PK and FK. Scripts had been generated exactly as I have expected them to have. Probably it worth to swallow the inconvenience in the usage of the logical model and use TOAD Data modeller in production. :slight_smile:

Message was edited by: Red Yanik


#5

What I think is,
that in logical model it is displayed the right way.
Things from physical model (like foreign keys) are a ballast in logical model.
Because you have the relationship there that says everything in logical meaning.


#6

I would have to concur. I’ve been data modeling for quite a while (~10 years) and in database development longer than that. To me, the graphical representation of the database is a much appreciated by-product of the guts that are behind the scenes to most of the data layer developers. As such, I’ve never concerned myself too much with the logical representation of the model (logical data model - LDM). I’m usually working from a reverse engineered database that I have to maintain or re-engineer. Therefore, I’m more interested in what indexes are where and what the cardinality is between tables in the physical data model (PDM)

There is a lot that can be inferred from the LDM if you know how to make the inference. If you are using a natural key for the parent table, there is no need to replicate those columns in the child table. You get those for free with the relationship reference. The real meat of the child table is what is there that isn’t part of the foreign key. If it’s an identifying relationship, what plus the foreign key makes this table unique. If it isn’t an identifying relationship, what is the primary key for this table. Depending on the audience, the primary and foreign keys may not even be important.

There in an enhancement request in here. After you have converted a LDM to a PDM, changes made to the PDM should be reflected in the LDM. If there is too much overhead in synchronizing them on the fly, there should be a converter to take a PDM back to a LDM much like you can convert a LDM to a PDM.


#7

I agree that in most cases PDM is enough. I’m in “Database world” also for a long time but my last 10 years was coloured exclusively in “Oracle red”. For that period I can remember me only 2 times when I have started from the LDM. In most cases it was: re-engineering and changing. But now we need to make connections to the varicoloured universe and I think the common start point can be LDM. In such case I would like to have as much possibilities as possible to be flexible and to adjust myself to others (if I see that this is the easier way to the end result).

   As for converters PDM -> LDM, I thing it is a logical task that have more then one answer. That means such converters either will produce just someone’s personal opinion how it should looks like, or will be too complex for use, that the manual changes will be preferred.