Today’s question:

Please let me know how can I represent relation between view and base table in ER diagram created using sql datamodeler version4.

So I know what the person is getting at here. They want to see the dependencies. Generally speaking, there are no actual relations between views and tables.

Exception: you CAN define foreign key constraints on VIEWS, but I digress. Skip to the end if you want to see how that works in the modeler…

The real requirement is someone needs to visually see how their views draw on data from other views and tables.

Data Modeler’s diagram only shows the ‘relations’ in a Logical diagram or the foreign keys in a relational model – not any dependencies. So you won’t see lines drawn from tables to their associated DML triggers.

But.

I do have SOME good news for our questioner – and maybe you, the reader.

We can show more.

Base Tables from View Query

At the bottom of the diagram widget, are listed the known tables
At the bottom of the diagram widget, the known tables are listed

I say ‘known’ tables, because this view actually draws from 5 tables, but the modeler doesn’t know about them, because I didn’t import them.

Now, you might not see these in your diagram, especially if you did a reverse engineer. What you might see instead is this:

Warning!
Warning!

We don’t parse the views as we import them. To know what is behind a view, we have to get the query underneath it, and see what’s what. Now imagine you don’t have 2 views like me, but hundreds. So for performance reasons, we let you parse these as needed to save beaucoup amounts of time.

Note, this only applies to reverse engineers, if you design everything from scratch, we already know what we need to know in the diagram.

So, to ‘fix’ the mystery views and to see the tables, do this:

Do this
Do this

Note – I highlighted the wrong thing, do the ‘Parse…’ not the ‘Validate…’

And then you’ll see this – you might need to resize the diagram object(s) to see the tables listed in the views.

The two tables used to compose this view.
The two tables used to compose this view.

But I need a picture with lines!

I hear ya. So let’s click a few more times.

Open the properties for the view.

Where it says 'Query' - that's a button - click it!
Where it says ‘Query’ – that’s a button – click it!

And now you get the visual representation of the query for your view.

Print this if you need it.
Print this if you need it.

I hope this helps.

Now, about those views with foreign keys…

We support showing these relationships in the diagrams for version 4.1 and higher. So, if you have a foreign key defined on your views, we’ll show those.

Yes, you can define referential integrity constraints on a view.
Yes, you can define referential integrity constraints on a view.
Why do this? Well,  maybe you are abstracting out the tables for your users but you want them still to know how the data is related.
Why do this? Well, maybe you are abstracting out the tables for your users but you want them still to know how the data is related.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

8 Comments

  1. Christian Rackerseder Reply

    Hi Jeff,

    I tried this with data modeler (Version 18.1.0.082, stand-alone).

    After having imported one view and one of its base tables I see
    – the imported base table mentioned in the bottom of the view’s box
    – no warning icon on the top of the view’s box
    When I press ‘parse sql’ nothing happens. It returns immediatelly.
    The Logs / Console do not show anything indicating a problem.

    Has this functionality been changed?

    Thank you
    Christian

    • Sorry, what are you expecting to see? Is the view hitting other tables that the PARSE isn’t catching?

    • Christian Rackerseder

      oh sorry to be unspecific.

      Yes the View is made of several tables.
      I imported the view itself and one of its base tables.
      I expected the other base-tables to be imported after selecting the parse-command
      from the context menu.

      Just as you decribed it.

    • Christian Rackerseder

      Hi Jeff,
      your question about what I would have expected to see was very eligible.
      In the meantime when trying with older versions of data modeler I found that I missunderstood what you were explaining.
      I thought that with sh.profits you showed some auto-import-feature like
      “Parse sql –> find tables that the view depends on and load them from the DB”
      Instead it seems to be: “find tables that the view depends on and if they are already loaded
      then note them in the bottom of the green box”

      Next (regarding the lines that show the dependencies) I understood that you opened
      the properties dialog to set some stuff to make the gui to draw the lines in the view with
      the green and yellow boxes. I did not recognize those motif style (on solaris) windows as
      that what you were aiming for.

      To make a long story short: It works as described. Only the small exclamation mark does not
      show up. (Version 4.1.5 was the last one were I saw it.)

      Regards Christian

  2. Hi Jeff,

    I have parsed sql for my views and in Relational model, I can see the dependent tables as mentioned by you. But I still see the warning symbol Triangle icon for my views in logical data model. Why is it? I have engineered from Relational to logical. still I see the warning symbol.

    Regards,
    Sushma

  3. Hi Jeff,

    I have parsed sql for my views and in Relational model, I can see the dependent tables as mentioned by you. But I still see the warning symbol Triangle icon for my views in logical data model. Why is it? I have engineered from Relational to logical. still I see the warning symbol.

  4. Hi Jeff,
    Let’s say that I create foreign keys on views (or on materialized views) to point to parent records in “regular” oracle tables. I don’t need these foreign keys from the data integrity point of view, I just need them so I can see in my data model relational diagram the lines between the (materialized) view and the tables that the view is based on. Are these new foreign keys on my materialized view going to affect performance of view operations like “create”, “refresh”? Is it going to make these views slower to work with? My materialized view stores 10 millions of records.
    Thank you,
    Milan

    • so create them, but disable them

      and then test 🙂

      In many cases knowing how the data is related allows the optimizer to make better decisions – so it could make it faster. But it doesn’t sound like that would happen in this scenario based on what you’re telling me.

Write A Comment