While going through Database architecture I came across Schema's . I am very much confused with:
Logical schema
Physical schema
How these two schema exist in the Database?
How can these be manipulated according to need?
How Logical schema and physical schema are related?
Best Answer
A logical schema won't exist in your database. A logical schema is a design-centric database structure built to meet your business requirements. It is a model that exists on a white board or in a diagraming tool. It is like the architect's drawings of your database.
A physical model is what is actually implemented in your DBMS.
The two can be different for a variety of reasons and in several ways:
Your logical model should be properly normalized, but your physical model may have denormalization which you've added deliberately and for all the right reasons.
Your physical model may have different naming conventions. Some people use plain English (or the language of choice) for their logical models and impose a more "system-ish" naming convention in their physical model.
Your logical model may have many-to-many relationships. Physical models implement m:n using intersection tables.
Your logical model may only use natural or business keys. Physical models may also add surrogate keys.
You should have a logical model because it lets you think about your database design without having to get bogged down in physical constraints. You need to have the physical model because that will be where your data eventually lives.