Slide 1
Slide 1 text
Oracle DBA 2020 Data
Masterclasses
Day 1 – Data Modeling
Jeff Smith
Distinguished Product Manager
Email: [email protected]
Blog: https://www.thatjeffsmith.com
@thatjeffsmith
Slide 2
Slide 2 text
Today’s Format
• ~60 minutes presentation
• Question? Submit to the Q&A Panel
• Yes, this is being recorded
Slide 3
Slide 3 text
whoami/ select USER from DUAL
• Database Tools
• Contact me for a private session for your company
Slide 4
Slide 4 text
Learn by Watching
• 70+ Videos
• Training
• Tips & Tricks
• 5 – 60 minutes
YouTube Channel Link
Slide 5
Slide 5 text
What are we doing here today?
• Database Training from the Database Team
• Part I: Design
• SQL Developer Data Modeler
• Modeling & Product Tips
Slide 6
Slide 6 text
What comes “first?”
• Business problem/opportunity
• Design
• Code (spec, code, debug, build…)
• Test
• Publish
• Support
DBAs ‘fall into’ a project at Delivery – aim for Design!
Slide 7
Slide 7 text
• We’re experienced
• We know better
• We’ll fix it later, let’s just get a POC going
• What’s in a name, really?
Why do we frequently skip the design step?
Design SHOULD INCLUDE a proper Data Model!
Slide 8
Slide 8 text
What’s in a name?
FIRST_NAME: VARCHAR2(20)
LAST_NAME: VARCHAR2(25)
We all ‘know’ how to store a person’s name, right?
Slide 9
Slide 9 text
We can’t hire this rock-star b/c the data model…
RIP, sweet Prince.
Slide 10
Slide 10 text
Modeling doesn’t start here
We tend to ‘just start coding’ before we ‘know’ the data
Slide 11
Slide 11 text
DBAs should be in the room, listening!
Modeling starts here.
Slide 12
Slide 12 text
From notes to a logical model?
ENTITIES vs
TABLES
Describe what defines
an employee, or even
just a PERSON
ENTITY RELATIONSHIP DIAGRAM
ERD
Slide 13
Slide 13 text
Super/Sub Type Entities
OTN Tutorial Link
• Common attributes
• For any ‘person’
Slide 14
Slide 14 text
But Jeff, We’re DBAs
This smells very ‘developer’ to me
Slide 15
Slide 15 text
I’ve seen things you people wouldn’t believe
• No foreign keys
• Foreign Keys w/o indexes
• DATEs stored as strings
• No security or logging
Like tears in the rain. Time to die.
Slide 16
Slide 16 text
Speaking of Unindexed FKs…
That’s not just an Oracle thing
Slide 17
Slide 17 text
DBAs are a wealth of knowledge
• Make the database USABLE for developers!
• Less ‘no’ and more ‘lets go!’
• After the schemas are ready, help them with their REST APIs
• Participate in code/design reviews
• Help setup test/dev/UAT environments
• You drink SQL & you know things.
Your talents are wasted if you’re only rebuilding indexes or taking backups.
The Shadow “knows” IT
‘No’ leads to Shadow IT projects
Slide 18
Slide 18 text
Offer to Review the Data Model
• Look for ‘red flags’
• Red flag #1…
THERE IS NO DATA MODEL
Slide 19
Slide 19 text
Some application basics
Some basic Data Modeler feature tips to follow
Slide 20
Slide 20 text
SQL Developer vs Data Modeler
• Same features in both, but…
• SQLDev – Modeler shares Menus & Resources
Advice: use standalone Modeler for big projects/tasks
Slide 21
Slide 21 text
Logical
Relational
Physical
Relational
Physical
Designs & Models
Design: One Logical, One+ Relational, One+ Physical
Slide 22
Slide 22 text
Your Design File(s)
• Design => Design.DMD file
• This is an XML file with pointers
• Rest of the files will be in a Design Directory
• Each object, attribute, line, EVERYTHING gets a
file
Trick: Find your work, File > Recent Designs
Slide 23
Slide 23 text
Your Designs, On Disk
Slide 24
Slide 24 text
The Basics: 101 Boot Camp
• Creating a table/entity
• Drawing a relationship
• Navigating
Slide 25
Slide 25 text
Creating an Entity/Table
Slide 26
Slide 26 text
Creating an entity/table
Switch to Overview tab for quicker editing
Slide 27
Slide 27 text
It’s NOT Too Early to Consider Security
In fact, it’s never too early to consider security.
PII: Personally Identifiable Information
Slide 28
Slide 28 text
More on Security
Granting PRIVS to Users and Roles
Slide 29
Slide 29 text
“Testing” the Table, did we get it right?
I did NOT want to add the WITH GRANT OPTION, FAIL!
Slide 30
Slide 30 text
Even more on Security
Quis custodiet ipsos…
LINK: Modeler can auto create JOURNAL table/trigger
Slide 31
Slide 31 text
Pop Quiz!
Foreign Keys are Bad for Performance?
DW: Keep the FK, but disable with RELY
Slide 32
Slide 32 text
Drawing the relationships
1. Start with parent
2. Click on table and let go
3. Click on other table
4. Confirm properties
LINK: Animated GIF Demo
Slide 33
Slide 33 text
Getting picky about lines…
Link: Tweaking the Lines
Slide 34
Slide 34 text
Performance – About those Indexed FKs
Design Rules
Link: Design rules and sample code
Slide 35
Slide 35 text
More UI 101: Navigating your designs
• Navigator
• Search
• SubViews
Models can be BIG – make it easier to see/find stuff
Slide 36
Slide 36 text
Search (and Replace!)
Great for Reporting, or Just Answering Questions about your Designs
LINK: Learn more about Search Feature
Slide 37
Slide 37 text
SubViews
Split things up – easier to view, understand, and work with
Click YouTube pic for VIDEO LINK
Slide 38
Slide 38 text
We have a database, but our design…
This happens way too much!
Slide 39
Slide 39 text
Reverse Engineering, one or more schemas
Turn your Database (or DDL Scripts) into a Model.
Slide 40
Slide 40 text
Let’s preach some more
Tip: Follow @HeliFromFinland!
Slide 41
Slide 41 text
Data typing and domains
So many ways (some wrong!) to store a DATE.
Slide 42
Slide 42 text
You can help your designers with domains
LINK: More on DOMAINs, example for JSON
Slide 43
Slide 43 text
Controlling/Managing Domains
• Stored in a separate XML file, separate from your design
• Import them, or copy to the domains directory
• SOURCE CONTROL THEM
Slide 44
Slide 44 text
Play favorites…save time and scrolling
• Data types…TOO MANY!
• Filter with Preferred
LINK: More on Preferred Types (and Domains)
Slide 45
Slide 45 text
Solo or team, you need to version your work
•
Subversion or Git
•
Checking in Design using Data Modeler
•
Multiple users check out designs
•
Pending Changes dialog
provides feedback to all users
•
Commit changes to repository
•
Manage conflicts
•
Review/Manage Version History
Using Git with Data Modeler (Docs)
Slide 46
Slide 46 text
Compares
• Model to Another Model…or different version of a model
• Model to a Database
• Database to a Model
• Delta/Alter Scripting
LINK: All about Compares
Slide 47
Slide 47 text
No content
Slide 48
Slide 48 text
Compares => ALTER Scripts
Slide 49
Slide 49 text
Compare with a Database
Compare Model with DB or DB with Model
Slide 50
Slide 50 text
Sharing your work with others
• Some folks will want pictures
• Or data dictionary reports
• Or will just want to write SQL
Slide 51
Slide 51 text
Pictures => HTML/SVG
Slide 52
Slide 52 text
Reports
• Standard
• Based on Search Results
• Reporting Templates
Slide 53
Slide 53 text
Reporting Repository: SQL!
Slide 54
Slide 54 text
Lots of features, but also lots of resources!
• Forums
• Books
• Docs
• Blogs
• YouTube
LINK: The Definitive Guide to SQLDev Data Modeler