definition of physical vs logical database model

What is the difference between physical and logical database design model ?Logical Models define Entities, their Attributes, Relationships between
Entities & Business Rules in a database design. The process of developing a
logical model usually engages the process of "normalising" the model, to
identify & remove redundancies, identification key dependance entity
atomicity.
You might consider the Logical Model the "design" of the database, without a
physical implementation - eg you define the table names, column names,
primary & foreign keys etc but without necessarily creating Data Definition
Language statemets (eg CREATE TABLE.. etc) for any specific database
platform.
The Physical Model is where you take the Logical Model & design the actual
DDL for a specific platform (eg SQL Server). This means defining the
physical implementation of the logical design - such as choosing precise
data types, developing stored procedure implementations (eg to implement
Logical Model business rules), writing CREATE TABLE statements etc.
It is not uncommon for the tables in a Physical Model to differ from the
Entities in a Logical Model. Often this is due to scalability / performance
considerations. Eg - you might have an Entity in the Logical Model that has
many attributes, but instead of implementing this as one Table in the
Physical Model with many columns, you might create two tables - one with the
few columns that are expected to be used most often so that clustered
indexes are narrower, with the aim of achieving better overall performance.
You might also place a view over the top of those two tables so that the
Physical Model can "appear" to the application as identical to the Logical
Model, even though the Entity has been vertically partitioned into two
tables.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eNg$rD$fDHA.3324@.TK2MSFTNGP11.phx.gbl...
> What is the difference between physical and logical database design model
?
>|||I might add that, in addition to what Greg mentioned, the following are
typical physical design issues (an incomplete and random list):
* index designs
* choice of database/log files and their physical placement
* choice of filegroups and the placement of tables
* setting the database options
* performance tuning (e.g. denormalizing a table)
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23HVYAp$fDHA.3528@.tk2msftngp13.phx.gbl...
> Logical Models define Entities, their Attributes, Relationships between
> Entities & Business Rules in a database design. The process of developing
a
> logical model usually engages the process of "normalising" the model, to
> identify & remove redundancies, identification key dependance entity
> atomicity.
> You might consider the Logical Model the "design" of the database, without
a
> physical implementation - eg you define the table names, column names,
> primary & foreign keys etc but without necessarily creating Data
Definition
> Language statemets (eg CREATE TABLE.. etc) for any specific database
> platform.
> The Physical Model is where you take the Logical Model & design the actual
> DDL for a specific platform (eg SQL Server). This means defining the
> physical implementation of the logical design - such as choosing precise
> data types, developing stored procedure implementations (eg to implement
> Logical Model business rules), writing CREATE TABLE statements etc.
> It is not uncommon for the tables in a Physical Model to differ from the
> Entities in a Logical Model. Often this is due to scalability /
performance
> considerations. Eg - you might have an Entity in the Logical Model that
has
> many attributes, but instead of implementing this as one Table in the
> Physical Model with many columns, you might create two tables - one with
the
> few columns that are expected to be used most often so that clustered
> indexes are narrower, with the aim of achieving better overall
performance.
> You might also place a view over the top of those two tables so that the
> Physical Model can "appear" to the application as identical to the Logical
> Model, even though the Entity has been vertically partitioned into two
> tables.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eNg$rD$fDHA.3324@.TK2MSFTNGP11.phx.gbl...
> > What is the difference between physical and logical database design
model
> ?
> >
> >
>|||Thanks a lot.. So informational...So happy...
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:Ol3aB4$fDHA.2072@.TK2MSFTNGP10.phx.gbl...
> I might add that, in addition to what Greg mentioned, the following are
> typical physical design issues (an incomplete and random list):
> * index designs
> * choice of database/log files and their physical placement
> * choice of filegroups and the placement of tables
> * setting the database options
> * performance tuning (e.g. denormalizing a table)
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%23HVYAp$fDHA.3528@.tk2msftngp13.phx.gbl...
> > Logical Models define Entities, their Attributes, Relationships between
> > Entities & Business Rules in a database design. The process of
developing
> a
> > logical model usually engages the process of "normalising" the model, to
> > identify & remove redundancies, identification key dependance entity
> > atomicity.
> >
> > You might consider the Logical Model the "design" of the database,
without
> a
> > physical implementation - eg you define the table names, column names,
> > primary & foreign keys etc but without necessarily creating Data
> Definition
> > Language statemets (eg CREATE TABLE.. etc) for any specific database
> > platform.
> >
> > The Physical Model is where you take the Logical Model & design the
actual
> > DDL for a specific platform (eg SQL Server). This means defining the
> > physical implementation of the logical design - such as choosing precise
> > data types, developing stored procedure implementations (eg to implement
> > Logical Model business rules), writing CREATE TABLE statements etc.
> >
> > It is not uncommon for the tables in a Physical Model to differ from the
> > Entities in a Logical Model. Often this is due to scalability /
> performance
> > considerations. Eg - you might have an Entity in the Logical Model that
> has
> > many attributes, but instead of implementing this as one Table in the
> > Physical Model with many columns, you might create two tables - one with
> the
> > few columns that are expected to be used most often so that clustered
> > indexes are narrower, with the aim of achieving better overall
> performance.
> > You might also place a view over the top of those two tables so that the
> > Physical Model can "appear" to the application as identical to the
Logical
> > Model, even though the Entity has been vertically partitioned into two
> > tables.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:eNg$rD$fDHA.3324@.TK2MSFTNGP11.phx.gbl...
> > > What is the difference between physical and logical database design
> model
> > ?
> > >
> > >
> >
> >
>