Logical Design & Physical Design of Database
Logical design
Defining business entities, attributes for each entity, and relationships among entities.
The latter are sometimes referred to as cardinality and optionality rules.
For example,
a inventory store application might include entities for store, employee, customer, item and sales transaction. Each store can have multiple employees, but each employee might only be allowed to be a full-time employee in a single store. Similarly each sales transaction must have a single customer, and can have one or more items sold.
Logical data models (LDMs) are often produced in the form of an Entity Relationship Diagram (ERD), which uses notation for expressing entities as boxes and relationships as lines. Attributes either appear in the boxes or are listed for each entity. Relationships are lines between the boxes, with the cardinality and optionality expressed as a circle for zero (or optional), a vertical line for "one" and a crow's foot for "many".
Store -||---employs---0|< Employee
(a Store employs zero one or more Employees and an employee is employed by exactly one Store).
Customer -||-- participates in -|< Sales Transaction
(a Customer participates in one or more Sales Transactions and a Sales Transaction is participated in by exactly one Customer)
Logical modeling is not specific to the database engine or other technical platform. It is organizationally independent as well. Logical models are generally highly normalized. Logical models are often used to derive or generate physical models via a physical design process.
Note:
Some methodologies (such as ORM) refer to a technology-independent data models as Conceptual Data Models (CDM) and a Logical Data Model is constrained by technology constraints.
Physical design
Generating the schema for tables, indexes, default and check constraints, and views for a specific physical structure such as a database, file, or XML document.
A physical model is generally specific to the database engine and version and is often optimized for a specific application usage of the data. Physical models include data types for each attribute and can be normalized or de-normalized. Physical models can change after the application is deployed in the production environment.
For example,
indexes might be altered to tune the performance. New tables, constraints, defaults and rules might be added to enhance the application's feature set and enforce new set of business rules.