Thursday, October 01st, 2009

Database design: Conceptual  , Logical and Physical  design.

Database design is the process of producing a detailed data model of database to meet an end users requirement.

Qualities of a Good Database Design

  • Reflects real-world structure of the problem
  • Can represent all expected data over time
  • Avoids redundancy and ensures Consistency
  • Provides efficient access to data
  • Supports the maintenance of data integrity over time

Database design methodology has 3 main phases:

  1. Conceptual database design
  2. Logical database design
  3. Physical database design

1. Conceptual database design:

It is a process of constructing a data model for each view of the real world problem which is

independent of physical considerations.

This step involves  :

  • Constructing the ER Model
  • Check the model for redundancy
  • Validating the model against user transactions to ensure all the scenarios are supported

ER Modelling :

Pictorial Representation of the Real world problem in terms of entities (which have attributes)  and relations  between the entities   is referred as ER diagram.

Entities: An entity is a class of distinct identifiable objects or concepts

Relations: Associations among entities is referred as €œRelations€

Attributes:  Attributes are properties or characteristics of entities.

Please refer for more about ER diagrams.

2. Logical database design

It is a process of constructing a model of information , which can then be mapped into storage objects supported by the Database Management System.

This step involves:

  • Table Generation From ER Model
  • Normalization of Tables

Table Generation From ER Model

The Cardinality of relationships among the entities can be considered while deriving the

tables from ER Model into:


Entities with €œone-to-one€ relationships should be merged into a single entity

Each remaining entity is modeled by a table with a primary key and attributes, some of which may be foreign keys


€œOne-to-many€ relationships are modeled by a foreign key attribute in the table . This foreign key would refer to another table that would contain the €œmany€ side of the relation.


€œMany-to-many€ relationships among two entities are modeled by a third table that has foreign keys that refer to the entities.

Normalization of Tables

Normalization is a process of eliminating redundancy and other anomalies in the system.

Please refer for more details.

In most cases in the enterprise world , normalization upto Third Normal form would suffice.

In certain cases or some transactions it is desirable that certain table be denormalised  for efficiency in querying the database tables.In those cases tables can be in denormalised form.

3.Physical database design

The physical design of the database specifies the physical configuration of the database on

the storage media.

This step involves  describing the base relations, file  organisations, and indexes design used to achieve efficient access to the data, and any associated integrity constraints and security measures.

External Links:’s_12_rules–IAP-2002/64B3A7CB-FA1F-4749-869C-A5D96ABCBE50/0/lect52.pdf

Category: Databases
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses

  1. I believe what you composed made a lot of sense. But, think about this, what if you were to write a killer title? I mean, I don’t wish to tell you how to run your website, however suppose you added something that makes people desire more? I mean Database design – Conceptual Design , Logical Design , Physical Design. | Prowareness Developer's Blog is a little vanilla. You should look at Yahoo’s front page and watch how they create post titles to get viewers interested. You might try adding a video or a pic or two to grab people excited about everything’ve got to say. In my opinion, it would make your posts a little livelier.

  2. I enjoy this weblog, wonderful content material and I am going to bookmark this web site for future updates.

Leave a Reply


This blog is kept spam free by WP-SpamFree.