Got this question today and realized I didn’t really have a specific blog post that shows how to do that, so here we go!

First things first, we need to go back and talk about the logical vs relational vs physical models again in SQL Developer Data Modeler.

Logical Model – entities and attributes. We’re describing our data and how things are related. There are no tables yet. ERDs come from here.

Relational Model – you can forward engineer logical model into a relational – at that point, an entity will generally become a TABLE. But, is it partitioned, encrypted, or compressed? That’s not decided here, that’s decided in the…

Physical Model – this model doesn’t have a corresponding diagram, and will belong to a relational model. You can actually have multiple physical implementations of your relational model. I could have one SQL Server 2005 model and one Oracle 21c model and one Oracle 12c model … physical implementations of what we talked about and described in our relational model.

Your design is your DMD file, it can have one Logical model, multiple relational & physicals.

Need a primer? – here are my Masterclass Session Slides and Video.

Table in the Relational Model

Most folks will start here. I think you shouldn’t skip the logical design process, but, at some point you will end up in the Relations model.

Columns and types, but no tablespaces or users or compression or…

You can preview the ddl as you go, if you want, but we’re going to take it to the NEXT level, to the Physical model.

Right-click on your Physical Models page, and say ‘New’ – then pick your ‘Site’. The Site will determine the features available to you.

I already have a 21c physical implementation, but I could also have a 11g one…

Physical Model properties

Now, I could jump right in and assign stuff…but, I won’t have anything to choose from. For example, I need to define my tablespaces and schemas (Users) before I can assign them to a table. So I’ll do that.

You can quite literally go crazy here with the options…but I’m just doing the basics.

I’ll do the same for Users, I’ll create one call ‘JEFF’.

Then, if we go and look at the TABLE properties, from the physical model tree, I can now assign those values, and then preview the DDL –

JEFF schema prefix is there, and TABLESPACE clause is there.

These physical model properties will be used when generating DDL, doing compares, generating reports IF the model is open.

Data Modeler in general doesn’t want to open these by default because of just how BIG they can be. So you can use this as a trick of sorts, if you close the physical, and generate the DDL –

Physical model is closed, so no metadata defined there is avail to generate DDL.

If you reverse engineer a database

…you’ll get a physical model by default. AND each schema will come in as a user, and you’ll get a SubView for each schema.

A dirty secret – some of you build live in the database, and when you’re done, THEN you reverse engineer into a model and use that for pretty picture and reports. I’ll let you decide if this can really be called data modelling.

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.

Write A Comment