Logical Storage

Imagine trying to find a specific T-shirt in a massive, disorganized warehouse versus finding it neatly arranged in a specific section, on a labeled shelf, with a clear catalog entry. The first scenario is chaotic and slow, while the second is efficient and organized. Databases work much the same way! How we logically structure and arrange our data is crucial for managing it effectively, ensuring quick access, and maintaining its integrity. 

In this lesson, we’ll explore the foundational building blocks of logical data storage: schemas, tables, indexes, and views. Understanding these components is like learning how to properly organize that warehouse, making data retrieval and management a breeze.

By the end of this lesson, we will be able to:

• Understand what schemas are and why they are important for organizing database objects.

• Describe the structure and purpose of tables in a relational database.

• Explain what indexes are and how they help in speeding up data retrieval.

• Define views and understand their role in simplifying queries and enhancing security.

• Create and use these logical storage structures in MySQL.

Logical storage

When we talk about logical storage, we are referring to how data is organized and perceived by us, the users or administrators, rather than how it’s physically stored on disk. Think of it as the blueprint of our data. A well-designed logical structure is key to an efficient and manageable database system.

Schemas: Database’s organizational units

Imagine a large company like our OnlineStore potentially expanding to sell different types of products or even offering different services. If all data objects, tables, views, etc, were just thrown into one giant pool, it would quickly become unmanageable. We might have naming conflicts, security challenges, and difficulty in understanding which objects relate to which part of the business.

Schemas help us avoid this chaos. They provide a way to group related database objects together, much like folders on a computer organize files. This organization improves clarity, manageability, and security by allowing us to set permissions at the schema level. For instance, one department might only have access to its own schema.

In MySQL, a schema is conceptually a container or a namespace for database objects like tables, views, indexes, and stored procedures. When we create a database using the CREATE DATABASE OnlineStore; command, we are essentially creating a schema named OnlineStore. All the tables we defined (Products, Customers, Orders, etc.) reside within this OnlineStore schema. This helps in preventing name collisions; for example, we could have a table named ProductDetails in a Sales schema and another table also named ProductDetails in an Inventory schema within the same MySQL instance, without them conflicting.

In MySQL, the terms database and schema are often used interchangeably. The command CREATE SCHEMA is a synonym for CREATE DATABASE.

Sample scenario: The OnlineStore database

In our OnlineStore course database, the entire collection of tables (Categories, Products, ...