0

I've read through multiple Q&As attempting to intuitively understand the difference between logical schemas and physical schemas.

Unfortunately, I've only been able to take away the following vague definitions.

  • Logical refers to a high-level definition of data including tables, data types, etc.
  • Physical refers to the 'implementation', i.e. how data is actually stored.

Is this correct? And if so, is there a concrete example or two that can drive home these definitions?

edit: I know that this question has probably been asked many times over, but I'm specifically asking for an illuminating example.

Improve this question
0

1 Answer 1

Reset to default
1

I think this StackOverflow Answer probably defines the difference between the two pretty well.

Specifically these two points:

A logical schema is a conceptual model of the data. In relational databases, it's often platform-agnostic - i.e. the logical schema can, in principle, be implemented on any SQL database.

The physical schema converts the logical schema into an implementation that works on a particular database platform. Sometimes, this is a largely mechanical exercise, applying the right datatypes to the attributes...

So in other words, if you had a schema that represented MovieTheaters, you'd probably have a few tables Movies, TicketSales, ConcessionSnacks. The TicketSales table would probably have a TicketId column, a Price column, and a MovieId column. This high level detail is essentially your logical schema.

Once you start implementing this schema on a specific database system, take Microsoft SQL Server for example, and using features and data types specific to Microsoft SQL Server, is when it becomes a physics schema. For example, your TicketSales table's TicketId column could be a INT data type with a primary key on it, and the MovieId could be a BIGINT field with a many-to-one foreign key on it, and the Price column could be a DECIMAL(4,2). And the Movies table can have a unique constraint on the MovieTitle column to prevent duplicate Movies being inserted into it.

Improve this answer
2
  • Gotcha, so to clarify - a 'schema' that we see in a statement such as CREATE SCHEMA may include both logical & physical schema information. The logical schema info might be table names, field names, etc. And the physical schema info will be indexes, constraints, data types, etc.
    – Noah Stebbins
    Commented Jan 30, 2021 at 16:11
  • @NoahStebbins Yea I think that's a fair statement. The physical schema contains the logical schema in a sense because it is a concrete implementation of it. (The logical schema is an abstraction of the physical schema.) Though the CREATE SCHEMA statement in most SQL languages really just means create a new schema name. Schema names are usually a prefix to the objects (tables, views, etc) that belong under it (as a way to group related objects of the same schema). More so, you'll see the logical and physical schema in the CREATE TABLE statements, for example.
    – J.D.
    Commented Jan 30, 2021 at 16:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.