Author Topic: Linking a Logical data model to database builder model  (Read 254 times)

ermvu

  • EA Novice
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Linking a Logical data model to database builder model
« on: December 06, 2017, 09:14:02 pm »
I'm using Enterprise Architect for the first time. I'm trying to model a database and I'm struggling to understand how to 'link' conceptual, logical, and physical data models.

For example if I change the multiplicity between two classes in my conceptual model would it / could it change the logical model? or if I add an attribute to my logical model would it / could it change my physical model.

And how can I see inconsistencies between the different models?

Thanks


Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7752
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Linking a Logical data model to database builder model
« Reply #1 on: December 06, 2017, 09:33:31 pm »
Simply put: you can't.

You could use MDA transformations to go from conceptual to logical and from logical to physical.
An you could repeat the process when you change something in the conceptual or logical model.
But there's no way to see the inconsistenties, or get a preview of what the MDA transform would change.

A customer of mine had me build a custom add-in for this purpose: the EA Database Transformer
This add-in does the logical to physical transformation and gives a full preview of all the changes that would be applied when the transformation would be executed.
It currently supports DB2 with the transformation logic for this client.

Geert

Glassboy

  • EA User
  • **
  • Posts: 898
  • Karma: +52/-54
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #2 on: December 07, 2017, 09:23:14 am »
My comment on that is tools that link the various models all tend to make the conceptual and logical models become shadows of a physical model.

qwerty

  • EA Guru
  • *****
  • Posts: 8972
  • Karma: +136/-124
  • I'm no guru at all
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #3 on: December 07, 2017, 09:45:52 am »
I have to concur. I was pretty excited the first time using model transformation. But after a short time I noticed that those traces are more like Shelob's spider webs. I do no longer use model transformation and rather maintain CIM, PIM and PSM as self-standing - just linked through the power of human brains.

q.

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5882
  • Karma: +71/-79
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #4 on: December 07, 2017, 10:56:20 am »
My comment on that is tools that link the various models all tend to make the conceptual and logical models become shadows of a physical model.
If you start from the physical, that is often the case (the "Schema as Design" analogue to "Code as Design").  I've been fortunate to have been able to do some greenfield DB design using generators and careful separation of concerns and use of scaffolding injection made the physical model elaborations of the conceptual model.  The key as Uffe keeps reminding us was: Concistency, konsistency, consistensy! TMUffe - after Paolo

However, these were artisan generators, not using EA.

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!

Richard Freggi

  • EA User
  • **
  • Posts: 43
  • Karma: +1/-1
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #5 on: December 07, 2017, 12:13:44 pm »
I don't thins there is / should be an automatic linking of data models at conceptual - logical - physical (schema) level.  Going from one level to another you make a number of decisions about the model structure based on circumstances, your style and habits etc. There are many different correct ways to develop a physical schema from a logical one and vice versa.  Don't know how it could be automated to have changes propagated correctly.

ermvu

  • EA Novice
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #6 on: December 08, 2017, 08:07:15 pm »
Very useful information. Thank you all for your input

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 7752
  • Karma: +165/-21
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Linking a Logical data model to database builder model
« Reply #7 on: December 08, 2017, 08:26:02 pm »
I don't thins there is / should be an automatic linking of data models at conceptual - logical - physical (schema) level.  Going from one level to another you make a number of decisions about the model structure based on circumstances, your style and habits etc. There are many different correct ways to develop a physical schema from a logical one and vice versa.  Don't know how it could be automated to have changes propagated correctly.
This particular client has all their transformation rules defined.
The add-in transforms the logical model into a physical model following these transformation rules.
Then the DBA's review the proposed structure and overrule certain rules if needed.
The tool marks the database objects that have been overruled in order to not propose a change according to the standard transformation rules next time the logical model is being transformed.

In any case, this save a lot of mindless grunt work and helps with the standardization (naming conventions etc..)

Geert

Richard Freggi

  • EA User
  • **
  • Posts: 43
  • Karma: +1/-1
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #8 on: December 09, 2017, 01:33:34 am »
I don't thins there is / should be an automatic linking of data models at conceptual - logical - physical (schema) level.  Going from one level to another you make a number of decisions about the model structure based on circumstances, your style and habits etc. There are many different correct ways to develop a physical schema from a logical one and vice versa.  Don't know how it could be automated to have changes propagated correctly.
This particular client has all their transformation rules defined.
The add-in transforms the logical model into a physical model following these transformation rules.
Then the DBA's review the proposed structure and overrule certain rules if needed.
The tool marks the database objects that have been overruled in order to not propose a change according to the standard transformation rules next time the logical model is being transformed.

In any case, this save a lot of mindless grunt work and helps with the standardization (naming conventions etc..)

Geert

Geert: very impressive and also extra props to you for releasing it as open source!!!

Paolo F Cantoni

  • EA Guru
  • *****
  • Posts: 5882
  • Karma: +71/-79
  • Inconsistently correct systems DON'T EXIST!
    • View Profile
Re: Linking a Logical data model to database builder model
« Reply #9 on: December 11, 2017, 10:40:54 am »
I don't think there is / should be an automatic linking of data models at conceptual - logical-physical (schema) level.  Going from one level to another you make a number of decisions about the model structure based on circumstances, your style and habits etc. There are many different correct ways to develop a physical schema from a logical one and vice versa.  Don't know how it could be automated to have changes propagated correctly.
This particular client has all their transformation rules defined.
The add-in transforms the logical model into a physical model following these transformation rules.
Then the DBA's review the proposed structure and overrule certain rules if needed.
The tool marks the database objects that have been overruled in order to not propose a change according to the standard transformation rules next time the logical model is being transformed.

In any case, this saves a lot of mindless grunt work and helps with the standardization (naming conventions etc..)

Geert
(my emphasis)
I've found one of the first things to define in any automated transformation/generation process is the "escape mechanism" to allow you to specify exceptions.  As they used to say on Sesame Street "One of these things is not like the others..."

This is also the source of the (in English) phrases "The exception that proves the rule".

Paolo
Inconsistently correct systems DON'T EXIST!
... Therefore, aim for consistency; in the expectation of achieving correctness....
-Semantica-
Helsinki Principle Rules!