i want to know whether there is anything called logical schema and the conceptual schema of database table. if so what is it.i searched lot of places for this specific two words, but did not find anything helpful.i would be grateful to anyone who provide me definition with an example.Thank you :)
Disclaimer: This is just a personal interpretation based on how I think most people generally use those terms.
A conceptual schema is more of a "rough draft", basically just trying to identify the key tables that would be needed, perhaps a few key columns for each, and how they would relate to one another. This might happen in the early analysis phase, or during a high-level design phase.
Later on, in the more detailed design phase, a logical schema would be an attempt to identify all of the tables and columns that will be needed. Maybe not the "final draft", but much closer to it.
Below is a simplified example of each.
Conceptual Schema example:
- Customer (id, name)
- Product (id, sku, name, price)
- OrderHeader (id, date, customer id)
- OrderItem (id, order header id, product id, quantity)
- 1 Customer : 0..* OrderHeader
- 0..* Product : 0..* OrderItem
- 1 OrderHeader : 1..* OrderItem
Logical Schema example
- Customer (CustomerId, Name, CreatedOn)
- CustomerAddress (CustomerAddressId, CustomerId, StreetAddress1, StreetAddress2, City, State, Zip)
- CustomerPhone (CustomerPhoneId, CustomerId, PhoneNumber, Extension)
- Product (ProductId, SKU, Description, Size, Price)
- OrderHeader (OrderHeaderId, CustomerId, DatePlaced, Status)
- OrderItem (OrderItemId, OrderHeaderId, ProductId, Quantity)