Oracle DBA Masterclasses: Data Modeling

0ed10d1154c696886ca483fe827cb299?s=47 thatjeffsmith
September 23, 2020

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

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

September 23, 2020
Tweet

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
  47. None
  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