I have a nasty habit of not thinking. I take something for granted or assume something for so long, that it corrupts my original learning or understanding of a concept. And then when someone calls me out, my first reaction is to defend my ‘shortcut’ thinking instead of re-evaluating my set of assumptions.
So I’ve been working with data modeling software for almost 15 years and on a regular basis for the last 8 or 9 years.
When talking to customers, ninety-percent of the time, when they say –
Can your tool generate an ERD?
– they are asking for a diagram which displays and describes the PHYSICAL properties of their data model for their application. Which in this case is generally a diagram of their tables and views and the relationships between them.
This is NOT an ERD though.
The ‘E’ part of ERD is important. It stands for ‘Entity.’ Entities are a much more abstract concept than a table. An Entity is a 1000 ft view of how you are going to handle a uniquely identifiable piece of data – kind of like how an atom used be seen as the smallest physical piece of matter. In a logical model, I might call an ‘entity’ a ‘customer.’ When I go to build the system physically in a databse like Oracle, that might actually be composed of one or more TABLEs.
So the Logical or Entity based diagram is completely separate from the Physical diagram.
I have a nasty habit of calling THIS an ERD
When I do an import of a data dictionary (which is a live, breathing instance of Oracle in this case), SQL Developer Data Modeler builds for me by default a Relational Model which is also composed of one or more Physical models.
And I get a diagram!
And that’s USUALLY enough for what people are looking for.
But when someone says they NEED an ERD, I should know better to clarify what their requirements actually are. And to make sure that their idea of an ERD matches up to mine, regardless of who is ‘right’ or not.
An Actual ERD
In SQL Developer Data Modeler I can reverse engineer a Relational Model to a Logical one (it’s on a right-click context menu.) Once that’s done, THEN I have a list of Entities and Attributes AND the associated Entity Relationship Diagram.
So what’s the big deal?
Don’t call something by name if it’s not the right name. Or if you do ‘cheat’, be sure to clarify and explain what you’re actually referring to. Or someone who doesn’t really get what you are saying will hear your words and start building a shaky understanding of the concepts.
And THAT is how poorly normalized systems with no foreign keys and tables with hundreds of columns are born.
11 Comments
Hi Jeff,
I am been following your blog for quite sometime now and created some sample relational and ERD diagrams. However, I have a requirement for a specific client to create a schema doc. I am using sql developer data modeler to do reverse engineer and create the relational digram.
Now I am facing two specific issues –
1. Although I can see the relational diagram, I can’t see any relationship between the entities for that particular schema
2. I am unable to convert the relational model into ERD using the “Engineer to Logical Model”
Can you please tell me why it’s failing?
Thanks,
Indraneil S
1. do your tables/views have any foreign keys? if not, no lines for the tables/views
2. what happens when you try?
i’m also struggling to represent a constraint in a logical model. we know that we’ll need a check constraint for a particular entity. is there a way to capture that in a logical model so it gets generated for the relational model automationcally? else worry it’ll get forgotten… many thanks!
Open the attribute editor from the tree – you can add constraints there.
is there a way to show FK columns (the ones auto-created as part of defining a relationship) on the *logical* model?
yes, change the Notation style to something other than Barker. see the last bit here
most helpful, thank you!
Some months ago, I posted to ask about how to generate an erd. I was using an older version of sql developer. The government finally installed the newer version. It seems to have loaded ok and will start up. However, when I try to open a db connection, I get a 17002 error. IO Error: Got minus one from a read call
Any suggestions? The help desk asked if I was sure the listener was running, well I had sqlplus up and running, right beside dvlpr. So I’m asking for your help again. What I think it is, is that dvlpr doesn’t have permission to write to some directory for temp files – I do not have admin rights to my computer.
Googling found me this
http://www.trinidba.com/2012/05/02/sql-developer-vendor-code-17002/
You can also open a SR with MOS and they can talk you through it.
I think there is an error in this posting.
Under “An actual erd” this is the first sentence
“In SQL Developer Data Modeler I can forward engineer a Relational Model to a Physical one”
I think you meant logical model rather than physical model
I agree with you totally, Jeff. Its just not with the clients I keep facing these type of questions rather with the project management team who always ask for when will you give a data model and is this time enough to build the data model. That’s when I get into the explanation of details (educating) that there exists different type of data models – a CDM, LDM, PDM and that’s when its realized that a Logical Data Model (ERD) is different from Physical Tables. I wish it ends there, however next question comes when will give these two different models :)..I really hope all the time when will it be that folks learn difference, and understand how it has to be implemented. Maybe sometimes I talk to the wrong audience!
Thanks for this write up. Gives me better fighting power within self and with teams.