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.
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.