Toad World® Forums

Sync between Physical and Logical models


#1

Hi all,

Started using toad DM relatively recently, previously was on ErWIn DM. Have created logical model initially, then using sync and convert wizard generated new physical model and SQL script for the database. Physical required some additional work like naming the foreign key attributes properly and some other. Now the question is, when I need to make changes to the logical model and propagate them down to the physical, is there a way I can compare and get the delta into the physical model or do I have to re-create physical every time I need it?

Additionally, noticed that when I generated sql script from my physical model = sql server 2012, it did not generate indexes at all for any of the foreign key attributes by default.

Does not seem optimal, why can I not see foreign key fields in logical model?

Any advise please?


#2

Logical models by definition are conceptual - i.e. business models. There is no such thing in business as foreign keys, hence you cannot show them in logical model. When you convert a logical model into a physical one and choose a relational database like SQL Server or Oracle, then the mechanism to implement the business defined relationship between entities requires a foreign key - so you see them.


#3

Hello Alexey,

here you can find some additional tips or information:

Re synchronization: you cannot synchronize physical model with logical model.

(Logical model in TDM supports inheritance and there are three ways how inheritance can be converted to physical. More info: www.toadworld.com/…/inheritance-modeling-in-logical-model-and-why-fk-attributes-don-39-t-migrate-to-child-entities.aspx)

But you can synchronize logical model with existing physical. First, convert logical model to new physical. Then compare existing physical model with the newly converted and merge them. Use Sync & Convert wizard for that.

If you do not work with inheritance at all, use Universal model (generic physical). More information about model types:

www.toadworld.com/…/model-type-selection.aspx

Regards,

Vaclav


#4

Well, it is true only partially. Yes logical models are intended for the business stakeholders discussions, fair enough. But the sole purpose of one designing the logical model is to facilitate the successful implementation of the physical model or models.

Also the definition of a conceptual model is different from the one of the logical. Conceptual does not have attributes and only core entities and relationships and is there for ultra high level view where logical is not very far away from the actual implementation, although still platform agnostic

This is where I think ErWin does a better job keeping the fields for FK constrainst columns in its logical layer, as it is possible to keep using the logical as your primary source of analysis, aligning it to physical and making sure the changes you make in the logical model are easily reflected into the physical.

Here, the point I found difficult is that once you’ve created physical model and renamed the FK fields once, you seem to have no choice but to keep repeating the same over and over again as I couldn’t find an easy way to add a table or attribute in logical and then have this incremental change syncronised into physical. I had to re-create physical model from scratch, if there is a more elegant way of dealing with this issue, please let me know


#5

Hi,

I am not sure what you need to model, but if you want to create a model that is not platform specific and supports migration of foreign keys to child entities, use Universal Model instead of Logical Model. Easier synchronization with physical model is a big advantage of Universal Model.

I hope it helps,

Vaclav


#6

Thanks, I will play around with Universal model


#7

Maybe this will clear things up (and I’ve asked product manager to consider renaming):

General Concept What TDM calls it

====================================

Conceptual = Logical

Logical = Universal

Physical = Physical

So as Vaclav suggest, maybe you should use Universal.

PS - I worked at Logic Works, original creator of ERwin. I know it well - and it does not support the concept of true conceptual modeling. IT’s always been more of a database design tool rather than a true data modeling tool like say Power Designer. Plus it was bought by Platinum (when I left), then by CA and now by EMBT. None of the original people there anymore - and not sure what their direction has been last few years or where EMBT will be taking it (since they have their own tool - ER Studio - which is better than EWRwin).


#8

Interesting to know. Becomes clearer when you map them like this. Have created Universal model, which TDM calls physical. Think this will be the solution for my problem. Thanks everyone for your help


#9

(Post moved)