Instead of having a column of type VARCHAR2 for a ‘Country,’ you might want to create an COUNTRIES type. Said COUNTRY would have an ID, NAME, and LOCATION.
To make things even more fun, LOCATION would also be a custom type of LOCATIONS, having even more attributes/columns.
To model these from scratch, you would build them in your design as part of the Data Types Model. You could then take advantage of them in your Relational Model Diagram when defining the column data type.
If you reverse engineer an existing system, taking care to INCLUDE the TYPEs, then you can see exactly how the data is stored in your diagrams very nicely in Oracle SQL Developer Data Modeler.
So for a given example:
CREATE OR REPLACE TYPE location_typ AS OBJECT ( location_id NUMBER (4) , street_address VARCHAR2 (40) , postal_code VARCHAR2 (12) , city VARCHAR2 (30) , state_province VARCHAR2 (25) ) NOT FINAL ; / CREATE OR REPLACE TYPE nt_location_typ IS TABLE OF location_typ ; / CREATE OR REPLACE TYPE country_typ AS OBJECT ( country_id CHAR (2) , country_name VARCHAR2 (40) , locations nt_location_typ ) NOT FINAL ; / CREATE OR REPLACE TYPE nt_country_typ IS TABLE OF country_typ ; / CREATE TABLE region_tab ( region_id NUMBER , region_name VARCHAR2 (25) , countries nt_country_typ ) NESTED TABLE countries STORE AS nt_countries_tab ( NESTED TABLE locations STORE AS nt_locations_tab ) ;
Basically, create some types, and then some nested table of said types, and then use those as the data type definitions in a table, and then import that into a Relational model in SQL Developer.
Here is what you get:
The Relational Diagram, Complex Types Expanded
You can tell the Modeler to expand one level of complex types. In this case, instead of JUST seeing that the COUNTRIES columns is of type ‘NT_COUNTRY_TYP’, you can see that type as defined as a ID, NAME, and LOCATION.
I mentioned to our developer that it might be nice to allow for the nested types to be expanded further. He agreed and you can see this available in the next update, Cue the legal disclamer about promises on new features and how things are likely to change.
But for now, you can see the types much more clearly – today.
Since this is a nested type, you may want to see the relationship expanded even further. This is available with
The Data Types Model Diagram
Power Tip: Use the New Document Tab Group feature to see two diagrams, side-by-side.
If you open the data type properties, you can see exactly how each attribute is defined:
You can also see how things are setup in the…
The Physical Model
One more thing…
Want to Learn More about Super and Sub Typing?
Check out this nice SlideShare deck from our developer, Philip.