Data Modeling from the Trenches

Involve users early and often

Simplify.

Standardize

• Establish a naming standard

• Establish modeling standards

• Ensure the standard is followed

• aka Subject-Area or Business Models

• Only include the critical concepts within the scope of the model.

• The things mentioned over and over in conversations about the business.

• aka Relational

• the rules behind an area of business

• independent of implementation context

• use normalization and abstraction

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 - ?

Abstraction

• Design in flexibility by eliminating specifics.

• In short, making the model more generic and re-usable.

• no aka's

• logical model optimized for the implementation context

• software

• hardware

• data usage

• performance

• multiple points of failure

• slower and error-prone

• inflexible

• unstable

• poor data quality

• costly to maintain

• 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, updateable views, virtual columns, etc.

bcoulam@yahoo.com

www.dbartisans.com

www.sourceforge.net/projects/plsqlframestart/

Conceptual

Logical

Data Modeling

(from the trenches)

The Presenter

• Bill Coulam

• bcoulam@yahoo.com

• Programming C, Java and PL/SQL since 1995

• Andersen Consulting (PacBell, US West, AT&T) - San Francisco, Denver, Herndon VA

• New Global Telecom - Denver, CO

• The Structure Group - Houston, TX

• Church of Jesus Christ of Latter Day Saints - SLC, UT

• Speaking at Oracle conferences since 2001

• Passionate about excellent modeling and programming practices

Document

It !

Get another pair of eyes

Test with plenty

of dirty data

Reduce and re-use.

Don't repeat.

Physical

Model

Well

Duplication

is

Evil