How to Collect Tables into Schemas with SQL - dummies

How to Collect Tables into Schemas with SQL

By Allen G. Taylor

A table consists of rows and columns and usually deals with a specific type of entity in SQL, such as customers, products, or invoices. Useful work generally requires information about several (or many) related entities. Organizationally, you collect the tables that you associate with these entities according to a logical schema. A logical schema is the organizational structure of a collection of related tables.

A database also has a physical schema — which represents the physical arrangement of the data and its associated items (such as indexes) on the system’s storage devices. When you seethe phrase “the schema” of a database, it refers to the logical schema, not the physical schema.

On a system where several unrelated projects may co-reside, you can assign all related tables to one schema. You can collect other groups of tables into schemas of their own.

Be sure to name your schemas to ensure that no one accidentally mixes tables from one project with tables from another. Each project has its own associated schema; you can distinguish it from other schemas by name. Seeing certain table names (such as CUSTOMER, PRODUCT, and so on) appear in multiple projects, however, is common.

If any chance exists of a naming ambiguity, qualify your table name by using its schema name as well (as in SCHEMA_NAME.TABLE_NAME). If you don’t qualify a table name, SQL assigns that table to the default schema.