Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Save the Company by Modeling Data Right

No description
by

Bill Coulam

on 13 March 2017

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Save the Company by Modeling Data Right

Standardize
Establish a naming standard
Establish modeling standard
Choose a modeling tool
Choose a versioning system
Choose a DB DevOps tool
Ensure the standard, tool and systems are used
Logical
Physical
Save the Company
by
Modeling Data Right

Bill Coulam
dbsherpa.com and dbartisans.com
Twitter: @billcoulam LinkedIn: billcoulam
bill.coulam@dbsherpa.com
Programming in various languages since 1995
PL/SQL since 1995
Oracle data/database design and tuning since 1997
Andersen Consulting - San Fran, Denver, Herndon
New Global Telecom - Denver
The Structure Group - Houston
Church of Jesus Christ of Latter Day Saints - SLC
Speaker at RMOUG, IOUG, ODTUG and UTOUG since 2001
2015 Oracle Developer of the Year nominee
Passionate about best programming/design practices
Conceptual
Model
Well

The data model is the foundation of the system.
Ensure the model is done properly the first time
Ignore those pleading for more friendly columns, quicker data access, elimination of joins.
AFTER
the model is correct, normalized, and reviewed,
THEN

make it friendly
with views, materialized views, PL/SQL or RESTful APIs, updateable views, virtual columns, etc.
Reduce and re-use.
Don't repeat.
Test with plenty
of dirty data
Simplify.
Get another pair of eyes
Document It !
bill.coulam@dbsherpa.com
dbsherpa.com (blog)
dbartisans.com (repo)
sourceforge.net/projects/plsqlframestart/
github.com/bcoulam/plsqlstarter
Involve users early and often
aka Subject-Area or Business Models
Only include the critical business concepts within the scope of the model.
critical = things mentioned over and over in conversations about the business.
aka Relational Model
The data rules behind an area of business
Independent of DB implementation
Uses normalization and abstraction
Logical model optimized for the RDBMS implementation
software
hardware
data usage
performance
Attributes and entities added to support tools, audit & history, reporting
multiple points of failure
slower and error-prone
inflexible
unstable
poor data quality
costly to maintain
Normalization
1nf - entities get PKs
move repeating attributes to own entity
separate multi-valued attributes
2nf - minimal PK to identify entity
3nf - no hidden dependencies
remove derived data
create lookup entities
BCnf, 4nf, 5nf, 6nf - Never had the need
Abstraction
Design in flexibility by eliminating specifics.
In short, making the model more generic and re-usable.
EAV
OAV
Vertical
Open
entity-attribute-value
object-attribute-value
Victories and casualties...
Convoluted models - US West
EAV Horror - Structure Consulting
Dev-Friendly models - Structure Consulting
Models of screens - Physical Facilities Dept.
Deadline-friendly Developer Model - Missionary
Overengineered and too flexible - Missionary
Requirements Fuzzy - Missionary
Clean, flexible, normalized models
- PacBell, NGT, Structure, LDS Church
A Basket of Bad Ideas
A data architect cares about clean data because it produces correct, actionable business intelligence instead of questionable business intelligence.
A data designer asks the right questions at the right time, determining and documenting:
All the things
All the attributes of the things
All the relationships
Optionality of the attribute and relationship
Cardinality of the attribute and relationship
Data type of the attribute
Default value and valid values of the attribute
Minimum and maximum length of the attribute
Natural keys
Abstractions: Too specific? <-----> Too generic?
Performance requirements
Security requirements
How will the data be used
"There are only two hard things in Computer Science: cache invalidation and naming things." - Phil Karlton (Principle Curmudgeon @ Netscape until his unfortunate passing in Italy in 1997)
Correctly naming the entities and attributes that will handle business change and the march of time should be a personal quest.

The name should accurately reflect what the thing contains.
Know Thy Data
Praised as self-documenting and frees you from:
Joins & performance problems
Relational data constraints and issues
DBAs
Object-impedance mismatch
However, total flexibility and no schema (model) means:
Duplication and redundancy everywhere
Missing, invalid data
Inconsistency
DBA duties are now yours
Objects and their data should still be analyzed, documented and modeled
"Schema-Less"
Agile Data Modeling
Be involved and engaged at the right time with stakeholders, business analysts and the dev team.
Use a good modeling tool to capture, centrally locate and publish requirements and artifacts
Data Model-Driven: No manual DDL. Generate DDL and new diagrams from the modeling tool. Validate, version and share.
Use an automated DB migration framework
Modeling Tools
Idera ER/Studio
SAP PowerDesigner
CA ERwin
Oracle SQL Developer Data Modeler
Several that are embedded in DB IDE tools
< Live Demonstration of Physical Models>
(Images were removed to protect employer assets)
< Live Demonstration of Logical Model>
(Images were removed to protect employer assets)
< Live Demonstration of Conceptual Model>
(Images were removed to protect employer assets)
Duplication is Evil
"Friendly"

Agenda:
Hard-won lessons
Standards
Model Types
Bad Model Types
How to Model Well
War Stories
Modeling to please the user or developer
Full transcript