Difference in "Logical" and "Physical" DB Shemas?
-
What is the difference between the logical database schema and the physical database schema?
Thanks!
-
Uh oh, Feel a good debate coming on w/ this one.
I'm sure There will be a small difference of opinion, but here goes.
Some feel the Logical database schema is relegated to the "Entities and Relationships" including Data constraints Referential integrity etc. and Physical is the Tables themselves. But I like to think of it this way.
The logical database schema is the tables, Foreign keys, Constraints, Triggers and Stored procedures. (Code, and DDL)
Physical schema has to deal with # of servers, filegroups, Disk Drives, and where the tables are placed. (On which filegroup) and What filegroup is placed on each drive. The product of the database is Files. How to manage those files, and where to put them is part of the physical database.
-
Yeah, good discussion, then where does the conceptual layer fit?
-
I believe this is a dicussion dealing with Data Architecture.
Logical Model:
Logical data modeling is a graphic-intensive technique that results in a data model representing the definition, characteristics, and relationships of data in a business, technical, or conceptual environment. Its purpose is to describe end-user data to systems and end-user staff.
Various methods of data modeling exist, each using a host of conventions and tools. The most popular approach is called the entity-relationship (ER) approach, developed by Peter Chen in the late 1970s. Although a number of authors and tool designers have modified and expanded ER concepts, most still have a strong Chen flavor. Also, with the introduction of CASE tools, the number of diagrammatic conventions that the data modeler must master has increased sharply.
Data Modeling Objects
The three types of data objects--entities, attributes, and relationships--are the basic building blocks of modeling:
- Entities are persons, places, or things about which an organization wishes to save information.
- Attributes are the properties of entities.
- Relationships are verbs that describe how entities relate to each other.
There are three levels of data modeling. They are conceptual, logical, and physical. This section will explain the difference among the three, the order with which each one is created, and how to go from one level to the other.
Conceptual Data Model
Features of conceptual data model include:
- Includes the important entities and the relationships among them.
- No attribute is specified.
- No primary key is specified.
At this level, the data modeler attempts to identify the highest-level relationships among the different entities.
Logical Data Model
Features of logical data model include:
- Includes all entities and relationships among them.
- All attributes for each entity are specified.
- The primary key for each entity specified.
- Foreign keys (keys identifying the relationship between different entities) are specified.
- Normalization occurs at this level.
At this level, the data modeler attempts to describe the data in as much detail as possible, without regard to how they will be physically implemented in the database.
In data warehousing, it is common for the conceptual data model and the logical data model to be combined into a single step (deliverable).
The steps for designing the logical data model are as follows:
- Identify all entities.
- Specify primary keys for all entities.
- Find the relationships between different entities.
- Find all attributes for each entity.
- Resolve many-to-many relationships.
- Normalization.
Physical Data Model
Features of physical data model include:
- Specification all tables and columns.
- Foreign keys are used to identify relationships between tables.
- Denormalization may occur based on user requirements.
- Physical considerations may cause the physical data model to be quite different from the logical data model.
At this level, the data modeler will specify how the logical data model will be realized in the database schema.
The steps for physical data model design are as follows:
- Convert entities into tables.
- Convert relationships into foreign keys.
- Convert attributes into columns.
- Modify the physical data model based on physical constraints / requirements.
-
My two cents:
A logical schema is a picture representing the tables, columns, indexes, foreign key relationships, views, stored procedures, etc. as they would be physicalized. The physical schema is what actually gets created on a server.
The operative word here is "Schema". An ERD, strictly speaking, isn't a schema...it's a model. Talk to any data administrator and he'll talk your ear off telling you that his beautiful logical model has NOTHING to do with schema. Some in fact get their noses out of joint when us dirty tacky physical guys sully their beautiful logical model with actual data! And heaven forbid we recommend denormalizing for performance or ease of use. Oh, the humanity!
Anyway, the distinction (although valuable...don't get me wrong!) is a rather artificial one depending on whose book you like. So rather than split hairs, a good operating definition is logical = picture, physical = what's actually created. This obviates the need to worry about whether it's an ERD or a logical schema, and gives a good solid dividing line.
Let the debate begin!
-
Generalized view:
In ER tools (like ERWin and ER/Studio), the Logical model is the data model with no concern as to what platform the database is going to be run on.
The Physical model is that Logical model applied to one particular platform (i.e. SQL Server, Oracle, MySQL, etc.). It's basically the Logical model with specific data-types, referential integrity, etc. having to do with which vendor's database server you are running.
I'm sure there is a lot more detail involved, but this is a general idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply