Q

Logical and physical data models explained

I was hoping you could explain to us the differences between a logical and physical data model. Examples would

be very helpful.

The difference between a logical and a physical data model are hard to grasp at first, but once you see the difference it seems obvious. A logical data model describes your model entities and how they relate to each other. A physical data model describes each entity in detail, including information about how you would implement the model using a particular (database) product.

In a logical model describing a person in a family tree, each person node would have attributes such as name(s), date of birth, place of birth, etc. The logical diagram would also show some kind of unique attribute or combination of attributes called a primary key that describes exactly one entry (a row in SQL) within this entity.

The physical model for the person would contain implementation details. These details are things like data types, indexes, constraints, etc.

The logical and physical model serve two different, but related purposes. A logical model is a way to draw your mental roadmap from a problem specification to an entity-based storage system. The user (problem owner) must understand and approve the logical model. A physical model is the roadmap from the logical model to the hardware. The developer (software owner) must understand and use the physical model.

Next Steps

Dozens more answers to tough database design questions from Pat Phelan

The Best Database Design Web Links: tips, tutorials, scripts, and more

Have an administration or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!

Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.

Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in September 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close