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