SQL Terminology
A list of common SQL terms and definitions you'll find in the Platform.
The following terms are defined in MinistryPlatform specific language. Although we try to describe more universal concepts, these are not comprehensive or technically precise, and they don't apply equally in other contexts.
- Calculated Column
- Columns that are calculated rather than stored, such as Contact Age. These are defined in a table and you may use them in views. In MinistryPlatform, Calculated Columns are named with a double underscore, such as the __Age field in Contacts. You cannot edit them.
- Column
- An attribute of a record stored in a table.
- Correlated Subquery
- A subquery which refers back to the parent query for part of its criteria. In views, subqueries are almost always correlated.
- Field
- A field represents a column in a view. These can be columns in a table or calculated values. You may often hear this term used interchangeably with Column, but a column is only one type of field.
- Foreign Key
- When a record in one table relates to a record in another table, a special column is defined in the first table which requires a primary key value from the second table. This is called a foreign key. It is called "foreign" because it points to the key of a "foreign" table. Some foreign key columns are optional, so that a NULL may exist in the record instead of a key when no relationship exists for the record. See Primary & Foreign Keys.
- Function
- In SQL, a function returns a single value and you can use it in place of a field or value. A function may take one or more parameters used to calculate the value.
- Primary Key
- Each record in a table has a unique identifier stored in a special column known as the primary key column. These values in a record is called a primary key. In MinistryPlatform, the Primary Key Column is the first field and named for the table with “_ID” appended. It is called "Primary" because it is the most important key in a table or record. In MinistryPlatform, primary keys are auto-incrementing integers. See Primary & Foreign Keys.
- Record
- A record is a single row in a table. In MinistryPlatform, views can only show one row per record at most. Usually, a view displays fewer than one row per record because of the filter clause.
- Relational Database
- In Relational Databases, data is structured in related tables. Each table holds a type of record, and these are related through special columns known as Primary & Foreign Keys.
- Row
- A row represents one record in a table.
- Subquery
- A query inside another query.
- Table
- A table is a collection of related records stored in a highly structured format. Data is stored in columns and rows. Each row represents a record, and each column represents an attribute of the record. In MinistryPlatform, a page represents a table.
- View
- A query which uses calculations and filters to show one "view" of an underlying table (or tables). In MinistryPlatform, views represent different ways to look at a table. You can pre-sort views in a particular order, filter for particular values, and display fields from related tables.
- Table Lookup Convention
- A syntax specific to MinistryPlatform used to represent table relationships. This functions as a SQL extension when you create views, pages, tabs (sub-pages), processes, and so on. See Table Lookup Convention.