The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “data model design”

25 Aug 2013
17 Comments

Better Data Modeling: Are you making these 3 beginner mistakes in your data models?

There are lots of people in the database industry that end up building data models.

Some of them are very educated and well trained and are professional data modelers and data architects. (If that is you, you can probably skip this article)

Others have learned on-the-job with little or no training or education on modeling concepts or techniques. They may be database administrators or even programmers that got asked to produce a model diagram by their boss or project manager, after the project was delivered (but there was no data modeler on the project ever).

This article is for this last group of folks who may want to improve their knowledge or skill in data modeling.

So here are three of the most common mistakes I have seen over the years:

1. Only defining surrogate keys

Do all your tables have a primary key defined and that primary key is a single column integer generated by the system? That is a surrogate key.

Instead of that, you should be defining a natural, or business, key for every table in your system. A natural key is a an attribute or set of attributes (that occur naturally in the data set) required to uniquely identify a row in that table. In addition you should define a Unique Key Constraint on those attributes in the database. Then you can be sure you will not get any duplicate data into the tables.

CLARIFICATION: This point has caused a lot of questions and comments. To be clear, the mistake here is to have ONLY defined a surrogate key. i believe that even if using surrogate keys is the best solution for your design, you should ALSO define an alternate unique natural key.

2. Using Visio or PowerPoint to draw your data model diagrams

This is all too common when no data modeler has been hired for a project or there is a really tight (as in NO) budget. The result is a pretty picture that gets out of date very quickly and can’t help you generate DDL code to build (or rebuild) the database.

Instead of that, you should either invest in a real data modeling tool (like ERWin), or better still, get Oracle’s totally free SQL Developer Data Modeler (my favorite!). The point being with a real data modeling  tool, you can forward and reverse engineer your database tables, make changes, review them, generate DDL, etc.

3. Not reviewing the model with real business people

Build it and they will come does not work!  How do you even know you are using the right terminology or have defined the relationships correctly if you have not talked to a business person about their data and data needs?

Instead of that, you need to involve business users from the very beginning of the project. You need to either set up 1:1 interviews with key stake holders or better yet have group JAD sessions to discuss the project and have them help you define the data model. That is the best way to get buy in to the end result.

Just avoiding these three rookie mistakes can greatly improve your chance of success.

Bonus Tip:  The best way to avoid these and other common rookie mistakes is to use a pre-defined data model review checklist.

You can get a jump-start on your own check list by downloading  my Kindle book A Checklist for Doing Data Model Design Reviews on Amazon.com.

Get it here: http://www.amazon.com/Check-Doing-Design-Reviews-ebook/dp/B008RG9L5E/, or just search Amazon for author Kent Graziano.

Here’s to building better data models! Are you with me?

Kent Graziano

The Data Warrior

Posted in Data Modeling, Oracle, SQL Developer Data Modeler and tagged , , , , , , , , , , , ,
30 Sep 2012

Oracle OpenWorld 2012: User Group Sunday

Yes, today was the first day for #OOW 2012. Affectionately known to many of us as User Group Sunday. Along with a ton of other activities, this is the day the various Oracle user groups get to “own” the agenda and put together the sessions they think Oracle customers, and their members, might want to see.

By users; for users.

For the 2nd year,  ODTUG asked me to curate their agenda. I was fortunate enough to “recruit” some great track leads who invited and vetted speakers and sessions to fill five rooms for most of the day. It was quite successful. (Thanks for the hard work guys.)

I attended quite a few myself and captured a few photos and thoughts. I was tweeting all day so you can also go to Twitter and search on @Kentgraziano to see my twitter stream.

After checking in at the User Group kiosk, I went to my first session given by Gwen Shapira and Robyn Sands who spoke about Flexible Design and Data Modeling. Great topic. They gave some very practical advice on do’s and don’t if you want to be more agile.

“Just good enough” does not scale.

Plan for Change

Worst Practices for Database Design

If you want some more modeling best practices, check out my ebook on Amazon: http://www.amazon.com/Check-Doing-Design-Reviews-ebook/dp/B008RG9L5E/.

Next I went on to see Kellyn Pot’vin and Stewart Bryson do a DBA vs Developers show down with No Surprises Development.

Release Planning Questions

Best advice – practice your deployments several times before going live…

Next: Guy Harrison talked about Hadoop, Bug Data, and Exadata. This was a very helpful intro talk about the space. I have been trying to wrap my mind around Hadoop, NoSQL, unstructured data, etc. and how we deal with it. Lots of great diagrams and examples to help explain.

Google’s Software Architecture

The Hadoop Ecosystem

Sigh…more to learn.

Next was a very interesting session by Mark Rittman about the Oracle Endeca software and how it can be used in a BI environment and how it compliments OBIEE.

This gives a quick view of what is involved with the Oracle Endeca Platform.

Oracle Endeca Information Discovery Platform

It looks like a very interesting platform that uses key value pairs to store the data. This enables search and analytics on some realtively unstructured data stores (i.e., not relational tables)

Final talk of the day (for me) was Jon Mead telling us about how they helped a customer develop event driven analytics using ODI and OBIEE and the Oracle Reference Architecture for data warehousing.

After all this, a  little break and networking, then on to the opening keynote.

It started with the Corporate Sr VP of Fujitsu  who talked about some cloud applications they have deployed in Japan. They have the Agricultural Cloud project to help farmers be more efficient and bring more and better crops to market. They also have developed a Healthcare Cloud Service for optimizing patient care and early diagnosis.

Very cool cloud applications.

Last up was CEO, Lary Ellison who announced Oracle 12c and Pluggable Databases (to support cloud deployments). I had heard about these (under NDA) at the Ace Directors meeting so now I can share a few pictures related to those since it is now public information.

Oracle 12c

Bigger, badder, faster…

Oracle Cloud Ecosystem

Pluggable Database Architecture

With PDB, you can develop a plug and play database. Many cool applications for this one.

To end out the day, I went to the 9th annual Oracle ACE dinner hosted by Oracle at the St Francis Yacht Club. Great food, drinks, and networking was had by all. Then back to the hotel to write this blog post.

Now off to bed so I can swim the bay with some other crazy people tomorrow morning. Wish me luck. Brrr.

Later.

Kent

Posted in ODTUG, Oracle, User Groups and tagged , , , , , , , , | Leave a comment
23 Sep 2012
11 Comments

Five Days Only – Get it Free: A Check List for Doing Data Model Design Reviews

Later this week I travel to Oracle HQ for my first product briefing as an Oracle ACE Director. In celebration of this momentous event, I have decided to give all me readers and followers a gift:

For the next five days (Sept 24 – 28, 2012), my first solo Kindle book will be ON SALE for the low, low price of FREE!

Don’t delay. You can get it here: A Check List for Doing Data Model Design Reviews: Kent Graziano: Kindle Store.

In case you missed my earlier post about the book, here is a brief description:

Tired of crappy data models and whiney data modelers? Need to deliver a high quality design in a short period of time? Need a better way to enforce standards? As part of trying to be more “agile” in my approach to developing databases, I have adopted a concept from the agile world: peer reviews. Before any data model moves from analysis (logical model) into development (physical model), the development team needs to gather to review what the modeler has done. If the model passes the review (almost never on the first round), the physical model is constructed. The physical model is then subjected to a rigorous review as well (including metadata). Only then can DDL be produced and deployed. This guide book will discuss the actual modeling and design process I follow and give you a check list of questions to ask in any model review session. This is a “take no prisoners” approach that has left many a would-be data modeler in a withering heap, but in the end you will have solid models and designs that deliver value.

The book has been doing pretty good (sells for $2.99 normally) but it could do better. 😉 Currently it is #32 if you search for Data Modeling under Kindle ebooks.

Will you help me get it into the top 10?

[ Update: as of Sept 24, 2012 at 12:45 PM CDT the book is now #2 in the Kindle store for Databases! Thanks everyone. Let’s keep it rollin’]

[ Update #2: as of Sept 25, 2012 at 12:45 PM CDT the book is now #1 in the Kindle store for Databases! How long can we keep it there?]

Head on over to Amazon and get it today: A Check List for Doing Data Model Design Reviews.

Thanks a bunch. Hope you can put the information to good use.

Oracle ACE Director

Kent

P.S. Do me another favor? After you get the book (for FREE), please log back into Amazon and leave a review so other data modelers know if it is a worthwhile book for them to read.

P.P.S. Don’t forget to like this post! And click the Follow button (upper right) if you want to get my posts sent to your email directly.

Posted in Data Modeling, Oracle and tagged , , , , , ,
02 Aug 2012
2 Comments

Best Practice: How to Create the Best Data Model Ever

A good data model, done right the first time, can save you time and money.

We have all seen the charts on the increasing cost of finding a mistake/bug/error late in a software development cycle.

Would you like to reduce, or even eliminate, your risk of finding one of those errors late in the game?

Of course you would! Who wouldn’t?  Nobody plans to miss a requirement or make a bad design decision (well nobody sane anyway).

No modeler worth their salt wants to leave a model incomplete or incorrect.

So what can you do to minimize the risk?

Well, if you are designing relational database or data warehouse systems, you can do your part by implementing a best practice approach to developing your data models.

What you need is a simple, repeatable process for reviewing your models.

Conceptual. Logical. Physical.

Years ago, a client asked me to help them develop a review process for their new data architecture committee. One that even a non-modeler could follow.

It had to be easy to follow and repeatable.

A checklist of what to look for and what to ask the modeler to make sure they got the best possible model.

It worked like a charm.

I have been using and refining that check list ever since.

It is amazing how many issues I have found over the years using this approach.

And I usually found them in early stages. They were also usually pretty small issues that were easy to fix at that stage.

A missing attribute definition.

A missing business key.

Incorrect cardinality or optionality on a relationship.

Small, but they would have been costly to fix if we had built the database with the original design and started coding the application, then found the mistake.

I imagine that you could probably benefit from using my process and  having this checklist handy to set up your very own data model design review process. Am I right?

So I decided to publish it and make it available to all my loyal readers and followers (even you lurkers out there!). 😉

As of today you can get your very own copy of the process details,  pre-review questions, and the review checklist for both logical and physical models in the convenient Kindle format for a crazy low price.

This is way less than you would pay for me or any other data model consultant to build one for you.

Even better, if you have Amazon Prime you can get it for free via the lending library. So try before you buy (you really do want your own copy to keep, honest).

So head on over to  Amazon and check it out.

Will you do me a favor?

If you like it and think it can help your friends and colleagues at other companies, then please post a review and be sure to tell them about over email, LinkedIn, or Twitter.

BTW – You don’t have to own a Kindle to get my book. You can download a FREE Kindle reader to your PC, MAC, iPhone, or Android device. So don’t worry…just get the book and tell your friends.

Happy Modeling!

Kent

P.S., If you have any ideas for other little reports I could provide, leave me a comment in the blog. Thanks!

Posted in Data Modeling and tagged , , , , ,

Post Navigation

Newer Entries →