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
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:
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:
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.
But I need a picture with lines!
I hear ya. So let’s click a few more times.
Open the properties for the view.
And now you get the visual representation of the query for your view.
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.
8 Comments
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?
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.
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
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
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.
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.