Oracle DBA Masterclasses: Data Modeling

Far too often, data modeling is a process in which a distressed support person reverse engineers an undocumented database.

But putting together a proper data model shouldn't be a luxury—instead, it should be a requirement for any new database application.

Join this session where Oracle experts will demonstrate how using Oracle SQL Developer Data Modeler will lead to a more flexible, secure, and performant database.

DOWNLOAD the PDF for Working LINKS

Transcript

1. Oracle DBA 2020 Data Masterclasses Day 1 – Data Modeling Jeff Smith Distinguished Product Manager Email: Jeff.D.Smith@oracle.com Blog: https://www.thatjeffsmith.com @thatjeffsmith
2. Today’s Format • ~60 minutes presentation • Question? Submit to the Q&A Panel • Yes, this is being recorded
3. whoami/ select USER from DUAL • Database Tools • Contact me for a private session for your company
4. Learn by Watching • 70+ Videos • Training • Tips & Tricks • 5 – 60 minutes YouTube Channel Link
5. What are we doing here today? • Database Training from the Database Team • Part I: Design • SQL Developer Data Modeler • Modeling & Product Tips
6. 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!
7. • 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!
8. What’s in a name? FIRST_NAME: VARCHAR2(20) LAST_NAME: VARCHAR2(25) We all ‘know’ how to store a person’s name, right?
9. We can’t hire this rock-star b/c the data model… RIP, sweet Prince.
10. Modeling doesn’t start here We tend to ‘just start coding’ before we ‘know’ the data
11. DBAs should be in the room, listening! Modeling starts here.
12. From notes to a logical model? ENTITIES vs TABLES Describe what defines an employee, or even just a PERSON ENTITY RELATIONSHIP DIAGRAM ERD
13. Super/Sub Type Entities OTN Tutorial Link • Common attributes • For any ‘person’
14. But Jeff, We’re DBAs This smells very ‘developer’ to me
15. 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.
16. Speaking of Unindexed FKs… That’s not just an Oracle thing
17. 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
18. Offer to Review the Data Model • Look for ‘red flags’ • Red flag #1… THERE IS NO DATA MODEL
19. Some application basics Some basic Data Modeler feature tips to follow
20. SQL Developer vs Data Modeler • Same features in both, but… • SQLDev – Modeler shares Menus & Resources Advice: use standalone Modeler for big projects/tasks
21. Logical Relational Physical Relational Physical Designs & Models Design: One Logical, One+ Relational, One+ Physical
22. 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
23. Your Designs, On Disk
24. The Basics: 101 Boot Camp • Creating a table/entity • Drawing a relationship • Navigating
25. Creating an Entity/Table
26. Creating an entity/table Switch to Overview tab for quicker editing
27. It’s NOT Too Early to Consider Security In fact, it’s never too early to consider security. PII: Personally Identifiable Information
28. More on Security Granting PRIVS to Users and Roles
29. “Testing” the Table, did we get it right? I did NOT want to add the WITH GRANT OPTION, FAIL!
30. Even more on Security Quis custodiet ipsos… LINK: Modeler can auto create JOURNAL table/trigger
31. Pop Quiz! Foreign Keys are Bad for Performance? DW: Keep the FK, but disable with RELY
32. 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
33. Getting picky about lines… Link: Tweaking the Lines
34. Performance – About those Indexed FKs Design Rules Link: Design rules and sample code
35. More UI 101: Navigating your designs • Navigator • Search • SubViews Models can be BIG – make it easier to see/find stuff
36. Search (and Replace!) Great for Reporting, or Just Answering Questions about your Designs LINK: Learn more about Search Feature
37. SubViews Split things up – easier to view, understand, and work with Click YouTube pic for VIDEO LINK
38. We have a database, but our design… This happens way too much!
39. Reverse Engineering, one or more schemas Turn your Database (or DDL Scripts) into a Model.
40. Let’s preach some more Tip: Follow @HeliFromFinland!
41. Data typing and domains So many ways (some wrong!) to store a DATE.
42. You can help your designers with domains LINK: More on DOMAINs, example for JSON
43. Controlling/Managing Domains • Stored in a separate XML file, separate from your design • Import them, or copy to the domains directory • SOURCE CONTROL THEM
44. Play favorites…save time and scrolling • Data types…TOO MANY! • Filter with Preferred LINK: More on Preferred Types (and Domains)
45. 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)
46. 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
48. Compares => ALTER Scripts
49. Compare with a Database Compare Model with DB or DB with Model
50. Sharing your work with others • Some folks will want pictures • Or data dictionary reports • Or will just want to write SQL
51. Pictures => HTML/SVG
52. Reports • Standard • Based on Search Results • Reporting Templates
53. Reporting Repository: SQL!
54. Lots of features, but also lots of resources! • Forums • Books • Docs • Blogs • YouTube LINK: The Definitive Guide to SQLDev Data Modeler