-3

The is currently defined as,

A schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables).

This is confusing because it's not explicitly referring to SCHEMA nor is it explicitly excluding it. It seems questions tagged "structure described in a formal language supported by the database management system" would be subject to . And that's what we see with it. People that are asking for a schema are asking for questions.

When they're not, like in this case

It seems what they really want are Event Triggers/ DDL Triggers, and that has little to do with schema?

4

I think the misusage of the tag stems from people not reading the tag's usage guidance or the (non-existant) tag wiki excerpt. There there is the ambiguity of the tag itself, because of the two possible meanings of schema.

Database Schema

This if the primary explanation of the tag as it is supposed to be used on DBA.SE and is explained as:

The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases). The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database.[citation needed] These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language. A database can be considered a structure in realization of the database language.1 The states of a created conceptual schema are transformed into an explicit mapping, the database schema. This describes how real-world entities are modeled in the database.

Reference: Database schema (Wikipedia)

I guess this is similar to the intended usage for "our" tag.

Data Schema

This is the second possible usage for , when you forget to look at the tag's usage guidance.

Oracle Database

Oracles describes the use of schemas in its product as follows:

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

  • Clusters
  • Constraints
  • Database links
  • Database triggers
  • Dimensions
  • External procedure libraries
  • Index-organized tables
  • Indexes
  • Indextypes
  • Java classes, Java resources, Java sources
  • Materialized views
  • Materialized view logs
  • Mining models
  • Object tables
  • Object types
  • Object views
  • Operators
  • Packages
  • Sequences
  • Stored functions, stored procedures
  • Synonyms
  • Tables
  • Views

Reference: Schema Objects (Oracle | Docs | 12.1)

Microsoft SQL Server

SQL Server has its own definition which is explained in the following block of quotes.

A core concept of SQL Server security is that owners of objects have irrevocable permissions to administer them. You cannot remove privileges from an object owner, and you cannot drop users from a database if they own objects in it.

User-Schema Separation

User-schema separation allows for more flexibility in managing database object permissions. A schema is a named container for database objects, which allows you to group objects into separate namespaces. For example, the AdventureWorks sample database contains schemas for Production, Sales, and HumanResources. [...]

Schema Owners and Permissions

Schemas can be owned by any database principal, and a single principal can own multiple schemas. You can apply security rules to a schema, which are inherited by all objects in the schema. Once you set up access permissions for a schema, those permissions are automatically applied as new objects are added to the schema. Users can be assigned a default schema, and multiple database users can share the same schema.

Reference: Ownership and User-Schema Separation in SQL Server (Microsoft | Docs | .NET)

PostgreSQL

PostgreSQL's definition is defined as:

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

There are several reasons why one might want to use schemas: - To allow many users to use one database without interfering with each other. - To organize database objects into logical groups to make them more manageable. - Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

Reference: Schemas (PostgresSQL 9.1 | Documentation)

MySQL

MySQL doesn't seem to have the concept of data schemas, but if anybody would like to enlighten me, then please do so.

Recommendation

Instead of burninating I would like to propose that we split the tag into its two possible meanings/definitions.

  • (as synonym of )
    As per the current description found in the Usage guidance. Essentialy, we rename to and switch it to being a synonym of

  • Defined as: "Use this tag when referring to the grouping of objects into a logical data structure for the DBMS: Oracle, SQL Server and PostgreSQL.". This will be a new tag and might be used when referencing the relevant DBMSs.

  • data-schema seems like a bad idea to me, wouldn't sql-schema be much more descriptive (as that's where the functionality you're talking about is standardized -- in the SQL spec) – Evan Carroll Nov 16 '18 at 10:31
  • 1
    I guess your proposal is open for discussion. Seeing as we got rid of sql it might be best to not have new tags beginning with sql.... and besides that, who knows (apart from you and all those lucky people who got to read the ANSI SQL Standard) that the data-schema part has been defined in said document? – hot2use Nov 19 '18 at 12:35
  • We got rid of sql because it was vague (and there are other tags more applicable). sql- as a prefix would mean SQL noun, and serves and disambiguate (when we see SCHEMA we know what it means). data- as a prefix doesn't disambiguate, it requires further explanation in a tag wiki. – Evan Carroll Nov 20 '18 at 16:51
  • I still have to hold out this, I don't think it's a good to prefix-namespace SQL verbs into data-. That seems confusing. – Evan Carroll Nov 21 '18 at 21:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .