Column Types
All possible column types in Cinchy.
Common Fields
Name
Each column must have a unique name. They must also not conflict with system columns (even if you are not using Maker/Checker on the table).
Data Security Classification
Each column has a data security classification. This defaults to blank, and can be set to one of 4 pre-configured settings (Public, Internal, Restricted, Confidential) or additional options created in the [Cinchy].[Data Security Classifications] table by an administrator.
Currently there is no functionality tied directly to Data Security Classification - the tagging is just for internal auditing purposes. Future security settings will be tied to Data Security Classifications, rather than simply done at a column level.
Description
Each column can optionally have a description. The description is displayed when you hover on the column header in Data Management.
Common Parameters
Add to Default View
Checked by default. After saving your changes this will add the column to be displayed in the default table (All Data by default). Generally it makes sense to be checked since there should be a view where all columns are displayed.
If you need to hide a column from certain users or groups you can do so in table controls. It is usually best to have a view where all table columns are displayed.
Mandatory
Makes the column a mandatory field. You will not be able to save or alter a record in a state where a mandatory field is blank.
Unique
Requires all values in the column to be unique. Adding a new record or modifying a previous record into a state where it is a duplicate of another record will cause an error and cannot be saved.
If you need uniqueness across multiple columns instead (ex. First Name does not need to be unique, but First Name + Last Name needs to be unique), you can create an index in Design Table, add those columns and set the index to unique. If it needs to be more complicated, you can also create a calculated column and set that column to unique.
Multi-Select
Some fields can also be set to multi-select.
For example, the column Players in [Football].[Teams] can be a multi-select field since each team will have multiple players.
Allow Linking
Checked by default. This allows other tables to use the column as a link/relationship.
See Linking Data to get more context on how they are used.
You want to pick identifying columns for linking, such as IDs or Name. Generally you want to use unique columns, but in some cases it is a better user experience to pick an almost unique field for readability.
I.e. Full name may not be unique, but it is much easier to understand than Employee ID.
Allow Display in Linked Views
Checked by default. Some columns may not make sense for linking but can be useful to display when someone is choosing an option.
See Linking Data to get more context and tips.
Encrypt
If Data At Rest Encryption is enabled, you will see the option of Encrypt for columns. If this is checked, the column will be encrypted within the database. This is useful for hiding sensitive information so that people with access to the database directly do not see these fields.
There is no difference in user experience within the Cinchy platform. The data is displayed in plain text on the UI or via the query APIs.
Regular Columns
Text
Text columns have a maximum length, set to 500 by default.
These are equivalent to VARCHAR(n) data type in SQL.
Number
You can choose from 3 display formats for number - regular, currency, and percentage. You can also decide how many decimal places to display (0 by default). Note that these are both display settings, and will not affect how the number is stored.
These are equivalent to FLOAT(53) data type in SQL (default, 8-byte field).
Date
There are several Date column type display format options available in Cinchy:
MMM DD, YYYY (e.g. Oct 31, 2016)
YY-MM-DD (e.g. 16-10-31)
DD-MM-YYYY (e.g. 31-10-2016)
DD-MMM-YY (e.g. 31-Oct-16)
Custom Format
Custom data formatting provides additional flexibility in how dates (and times) can be displayed within a Date column type:
Please Note: the "Default Value" field is not mandatory and should be left blank (best practice). However, if populated you will not be able to clear the default date value provided to a "blank" data (no date). You will only be able to overwrite it with another date value.
These are equivalent to DATE() data type in SQL.
Yes/No
You must select a default value of yes (1) or no (0) for yes/no fields.
These are equivalent to bitdata type in SQL.
Calculated
A calculated column is evaluated using other fields on the record. It will also have a result type - which is the form in which the calculated results will be stored.
For example, you can have a column [Full Name] that is CONCAT([First Name], ' ', [Last Name]).
These are equivalent to computed columns in SQL.
Choice
You can create a choice column (single or multi-select) in Cinchy. In this scenario, you specify all your choices (1 per newline) in the table design. A user will only be able to select from the options provided.
Geospatial Columns
If you created a spatial table, you will have access to the geography and geometry column types. These columns also have the option to be indexed via Index in the advanced settings on the column itself.
Geometry
In the UI, this takes a well-known text (WKT) representation of a geometry object. You can modify or paste the WKT representation directly in the editor on the UI. Geometric functions can be performed on this column through CQL and calculated columns.
Geography
In the UI, this takes a well-known text (WKT) representation of a geography object. You can modify or paste the WKT representation directly in the editor on the UI. Geographic functions can be performed on this column through CQL and calculated columns.
Link Columns
Link columns allow you to establish inherent relationships with other records in other tables. See Linking Data for more details.
Hierarchy Columns
Hierarchy columns are simply link columns referencing the current table. Some example uses of hierarchies:
Related Software Changes
Manager
Last updated
