DBA Masterclass, Week 1: Data Modeling

This morning I had the pleasure of spending an hour with almost 700 DBAs from around the world, mostly in Europe, talking about the importance of proper design.

Yes, the session was recorded, and yes I’ll update this post with a link to the recording when it’s available.

The Slides

Q&A

During the webinar, I received a few dozen questions, which I’ve consolidated below, with my answers!

So, in the order in which they were received…

1. We are running Oracle SaaS and Oracle Cloud HCM and Oracle Fusion Financials Cloud…how can we generate an ERD?

In the Oracle SaaS Cloud, you’re ultimately paying for the Application, not a database. Of course, ‘under the covers’ is an Oracle Database, but for many of the Cloud solutions, subscribers aren’t given access to the database their data is stored in.

There are exceptions to this rule. I know several of the SaaS offerings provide an Autonomous Data Warehouse instance where you can setup analytic reports, etc based off of your application data.

You need one of two things:

1. access to your database
2. someone to give you a schema DDL extract

With either of those, SQL Developer Data Modeler can quickly create an ERD.

2. Any thoughts to adding support for Quick SQL to Data Modeler

To data modeler, no..to SQL Developer, yes. For Data Modeler, I would take the DDL that Quick SQL generates, and use that to create a Design with.

3. Now days everybody talks about “End of DBA/Applications DBA” 🙂 Is it really something we DBA worry about? Or just learn new Normal(Tasks) for DBAs to be a Future DBA 🙂

A very fair question. I don’t think DBAs are going away, however I do think they’re expected to pick up new responsibilities. I’d also be prepared for organizations to need fewer DBAs as more tasks are automated or especially as more databases are hosted with Cloud providers (OCI/Autonomous!)

4. Can you share a link to your Data Modeler YouTube videos?

You bet!

5. Do we need additional licenses to use this tool?

No, Oracle SQL Developer Data Modeler is included with your Oracle Database! There’s also the OTN License you have by downloading the tool from Oracle.com – there’s no additional cost for this product.

You do need a valid MOS Contract for at least 1 Oracle Database if you want to open a Service Request for SQL Developer/SQLcl/Data Modeler/ORDS/SQL Developer Web.

6. Shouldn’t Oracle come up with some sort of automated indexing for these foreign keys?

In fact, we have!

7. What is UAT short for?

My apologies, I use acronyms way too often. User Acceptance Testing – a schema/environment where serious user testing can occur. A ‘fancier’ TEST you could say.

8. Is this working for Autonomous Database cloud services?

Yes! Data Modeler can connect to your ADB instance (with the Wallet), but we also provide a basic data modeler set of screens in SQL Developer Web!

9. How can we track changes in our model if say, another DBA makes a change?

If the DBA has changed the database itself, I would use the Synch feature. if they’ve made the change to the design, then you could either compare the individual models, or hopefully they’ve changed the model in Source Control, then that would show in your design as Pending Changes.

10. Can you incorporate the security aspects of a model into a database that doesn’t already have them?

Yes! Use the Synch feature to do a compare with your database. Then generate a SQL script. Take that script, test it, then run on the desired database(s).

11. What does “Contains PII’ mean?

Personal Identifiable Information. Your name is PII. If I have a table of customer who have bad credit, and I can see that table, I can know my neighbor owes our company money. That’s not good. Bad example, here’s a much better definition.

By the way, this gets tricky. You’d be amazed what data can be used to identify someone.

12. Where and how can I install SQL Developer (Data Modeler) on my desktop?

There’s no installer, just a ZIP. Download it from Oracle.com, and unzip it onto your machine. You just need Java. If you have Windows, you don’t even need Java, just grab the ‘Windows 64-bit with JDK included’ option.

13. Does Data Masking require any additional licensing?

Yes. However, some Oracle Cloud subscriptions might include it? Be sure to attend Russ’ Security session in the Masterclass Series to learn more.

14. How does Data Modeler differentiate between Sensitive and PII data?

It doesn’t not really. It’s up to YOU to identify said data, and then it’s up to YOU how to protect it. This is often a legal matter, especially with medical data.

15. How do we protect our data models so that only DBAs can see them?

Well for starters, don’t let anyone have data dictionary acces to the database, because then anyone can reverse engineer it to a new model. And then otherwise, it’s about protecting access to the design files.

16. Foreign Keys may be bad if they’re not indexed!

So index them already. I’d rather have the correct data a few seconds slower than the wrong data, real fast. You can can have your cake and eat it too in this case.

17. Are Journal tables populated by triggers?

Yes, as an INSERT/UPDATE/DELETE happens, the trigger records the transaction user, the change they made, and often the old data BEFORE the change was made.

18. Can I recommend any courses or classes on database design?

Not personally no, but I would go for the free stuff you can find before paying for anything.

19. Do you have any advice on where to put business logic, as much as possible in the database or as much as possible in an application layer…or somewhere else e.g. in an integration platform layer? Of course it depends, but if you have any general advice in the matter 🙂

Oh yes, yes I do 🙂

Keep as much as the data work CLOSE to the data. So basically, don’t be afraid to use stored procedures, views, etc!

I have a talk/video on this.

20. Can we reverse-engineer an existing schema in Oracle and compare that with a design from SQL Server ? (i.e. compare schema designs across databases / platforms)?

Great question…yes, I just tried this for you 🙂 I just have to decide if I want to use Oracle or SQL Server for the data types for the basis of the compare.

21. Can we export the ERD as a document?

Yes, you can have the dictionary and/or diagrams exported as RTF, PDF, HTML, or just images.

22. Can you we use this tool for databases other than Oracle?

Yes, we have some basic support for SQL Server and DB2 as well. You can also import a ‘generic’ JDBC data source. The Compare feature can only generate DDL for an Oracle design however.

23. RESPECT! Every time I hear about SQL Developer it is growing bigger and bigger in my eyes. Really good work! It looks simple. How do you manage to hide that much functionality 🙂

Thanks Mom 🙂

But seriously, we have a very experienced team of developer who work very hard to deliver a lot of features without a lot of complexity. The nature of the database and subject matter itself makes this a challenge, so sometimes we have to compromise.

I appreciate your compliment – we try!

24. how will you design a database that must integrate with an offline app?

The app is on or offline, but you will have data – whether it’s cached in a browser, in a file on your device, or live in your database. How is that data going to be defined, represented, interpreted, etc? That’s the purpose of your data model.