Cinchy Platform Documentation
Cinchy v5.0 - v5.5
Cinchy v5.0 - v5.5
  • Data Collaboration Overview
  • Other Wiki Spaces
    • Cinchy Data Sync
    • Angular SDK
    • JavaScript SQK
  • Release Notes
    • Release Notes
      • 5.0 Release Notes
      • 5.1 Release Notes
      • 5.2 Release Notes
      • 5.3 Release Notes
      • 5.4 Release Notes
      • 5.5 Release Notes
      • 5.6 Release Notes
  • Getting Help
  • Frequently Asked Questions
  • Deployment Guide
    • Deployment Installation Guides
      • Deployment Planning Overview and Checklist
        • Deployment Architecture Overview
          • Kubernetes Deployment Architecture
          • IIS Deployment Architecture
        • Deployment Prerequisites
          • Single Sign-On (SSO) Integration
            • Enabling TLS 1.2
            • Configuring ADFS
            • AD Group Integration
      • Kubernetes Deployment Installation
        • Disabling your Kubernetes Applications
        • Changing your File Storage Configuration
        • Using Self-Signed SSL Certs (Kubernetes Deployments)
        • Deploying the CLI (Kubernetes)
      • IIS Deployment Platform Installation
        • Deploying Connections and the CLI (IIS)
        • Deploying the Event Listener/Worker (IIS)
    • Upgrade Guides
      • Upgrading Cinchy Versions
        • Cinchy Upgrade Utility
        • Kubernetes Upgrades
          • v5.1 (Kubernetes)
          • v5.2 (Kubernetes)
          • v5.3 (Kubernetes)
          • v5.4 (Kubernetes)
          • v5.5 (Kubernetes)
          • v5.6 (Kubernetes)
          • Updating the Kubernetes Image Registry
          • Upgrading AWS EKS Kubernetes Version
          • Upgrading AKS (Azure Kubernetes Service)
        • IIS Upgrades
          • v4.21 (IIS)
          • v4.x to v5.x (IIS)
          • v5.1 (IIS)
          • v5.2 (IIS)
          • v5.3 (IIS)
          • v5.4 (IIS)
          • v5.5 (IIS)
          • v5.6 (IIS)
      • Upgrading from v4 to v5
  • Guides for Using Cinchy
    • User Guides
      • Overview of the Data Browser
      • The Admin Panel
      • User Preferences
        • Personal Access Tokens
      • Table Features
      • Data Management
      • Queries
      • Version Management
        • Versioning Best Practices
      • Commentary
    • Builder Guides
      • Best Practices
      • Creating Tables
        • Attaching Files
        • Columns
        • Data Controls
          • Data Entitlements
          • Data Erasure
          • Data Compression
        • Restoring Tables, Columns, and Rows
        • Formatting Rules
        • Indexing and Partitioning
        • Linking Data
        • Table and Column GUIDs
        • System Tables
      • Saved Queries
      • CinchyDXD Utility
        • Building the Data Experience (CinchyDXD)
        • Packaging the Data Experience (CinchyDXD)
        • Installing the Data Experience (CinchyDXD)
        • Updating the Data Experience (CinchyDXD)
        • Repackaging the Data Experience (CinchyDXD)
        • Reinstalling the Data Experience (CinchyDXD)
      • Multi-Lingual Support
      • Integration Guides
    • Administrator Guide
    • Additional Guides
      • Monitoring and Logging on Kubernetes
        • Grafana
        • Opensearch Dashboards
          • Setting up Alerts
        • Monitoring via ArgoCD
      • Maintenance
      • GraphQL (Beta)
      • System Properties
      • Enable Data At Rest Encryption
      • MDQE
      • Application Experiences
        • Network Map
          • Custom Node Results
          • Custom Results in the Network Map
        • Setting Up Experiences
  • API Guide
    • API Overview
      • API Authentication
      • API Saved Queries
      • ExecuteCQL
      • Webhook Ingestion
  • CQL
    • The Basics of CQL
      • CQL Examples
      • CQL Functions Master List
      • CQL Statements Overview
        • Cinchy DML Statements
        • Cinchy DDL Statements
      • Cinchy Supported Functions
        • Cinchy Functions
        • Cinchy System Values
        • Cinchy User Defined Functions
          • Table-Valued Functions
          • Scalar-Valued Functions
        • Conversion Functions
        • Date and Time Types and Functions
          • Return System Date and Time Values
          • Return Date and Time Parts
          • Return Date and Time Values From Their Parts
          • Return Date and Time Difference Values
          • Modify Date and Time Values
          • Validate Date and Time Values
        • Logical Functions
        • Mathematical Functions
        • String Functions
        • Geometry and Geography Data Type and Functions
          • OGC Methods on Geometry & Geography Instances
          • Extended Methods on Geometry & Geography Instances
        • Full Text Search Functions
        • Connections Functions
        • JSON Functions
  • Meta Forms
    • Introduction to Meta-Forms
    • Meta-Forms Deployment Installation Guide
      • Deploying Meta-Forms (Kubernetes)
      • Deploying Meta-Forms (IIS)
    • Creating a Dynamic Meta-Form (Using Tables)
    • Creating a Dynamic Meta-Form Example (Using Form Designer)
    • Forms Data Types
    • Adding Links to a Form
    • Rich Text Editing in Forms
Powered by GitBook
On this page
  • Table of Contents
  • 1. About Linking
  • 2. Choosing a Linked Column
  • 2.1 Allow Linking
  • 2.2 Allow Display in Linked View
  • 2.3 Display Columns
  • 2.4 Link Filter
  • 3. Relationships
  • 3.1 1:1 Relationship
  • 3.2 1:Many Relationship
  • 3.3 Many:Many Relationship

Was this helpful?

Export as PDF
  1. Guides for Using Cinchy
  2. Builder Guides
  3. Creating Tables

Linking Data

When you create a column within Cinchy, you can choose to create a link column. A link column allows you to establish inherent relationships with other tables.‌

Table of Contents

Table of Contents

1. About Linking

Linking is done by the Cinchy ID, which is unique. When you create a link column, you select a column to link to. This is simply a decision on which field to show from the linked record. You should pick a unique field to link on to avoid confusion if possible.

Once a record is created, its Cinchy ID never changes. This means that modifying the row of data in the linked table will not change the relationship in your table to that row. This also means that if you did not use a unique column, even though the UI looks the same, you are actually linking to different rows.‌

2. Choosing a Linked Column

‌In general, you should only use unique columns as the linked column. This needs to be balanced with readability in other tables.

For example, Full Name might not be unique to every employee, but it is a lot more readable and understandable than Employee ID. In other cases, it makes sense to link via an ID and simply add a display column to show relevant information.‌

2.1 Allow Linking

‌To help other builders follow best practices of only linking to unique (or close to unique, such as Full Name) columns, you should un-check the Allow Linking checkbox for non-unique columns so they will not be able to use it for linking.‌

2.2 Allow Display in Linked View

‌If this option is unchecked, it prevents users from showing this column in another table.

For example, if you have an ID card # within an employees table, you may not want to display it to the rest of the company because it simply would not be relevant when they are linking to employees and want to see additional information (such as department, title, location). Arguably, a lot of these columns are also taken care of by access controls (since most people will not have access to view that column).‌

Generally unchecking this box should be done sparingly, as it does not impact the security of your data, only how convenient it is to see it.‌

2.3 Display Columns

‌When you select a record to link to on the Manage Data screen, it can be useful to see additional information about the records to ensure that it is the record you want to link to (Image 1). You can add additional display columns in the advanced options for link columns (Image 2).

When you type in the cell, all displayed columns will be searched through, not just the Linked Column (Image 3). (Green does not have a B in it, but #00B050 does so the Green record shows up)

‌

2.4 Link Filter

‌‌The link filter can be used to filter records from the table being linked. This is useful for reducing the options to only the typical use case. Commonly used for filtering the drop down to a list of active users or other resources, while not preventing someone from entering missing records with inactive resources.‌

Note that this is simply a display filter, it does not prevent other values from being entered as long as they are valid records in the linked table.‌

3. Relationships

‌You can define 1 to 1, 1 to many, and many to many relationships.‌

3.1 1:1 Relationship

‌Generally it is rare to link 1:1 relationships since they should usually be in the same table. For example, you would not have a separate table for Employee Phone Number and Employee Address, they would simply be two columns within the Employees table. However there are cases nonetheless where it makes sense, for example, a Keycard tracking table where each keycard has 1 assigned employee.‌

To enforce a 1:1 relationship within Cinchy, you set the unique constraint and leave it as single-select when creating a link column.‌

3.2 1:Many Relationship

‌A common relationship to have is a one to many relationship. For example, one customer can have multiple invoices.‌

To enforce a 1:many relationship within Cinchy, you want to create a link column in the table on the “many” side of the relationship (in the above example, in the invoices table) and leave the link column as single select.‌

3.3 Many:Many Relationship

‌You can also have a many to many relationship. For example, you can have multiple customers, and multiple products. Each customer can order multiple products, and each product can be ordered by multiple customers. Another example is books and authors. An author can write multiple books, but a book can also have multiple authors. There are two ways to express many to many relationships.‌

In the case of multiple customers and multiple products, we want to use orders as an intermediary table to create indirect relationships between customers and products. Each order has 1 customer, and each order has multiple products in it. We can derive the relationship between customers and products through the orders table.‌

To create a many:many relationship through a different entity, you want to create a table for orders. Within orders, you want to create a single-select link to customers and a multi-select link to products.‌

In the case of books and authors, it makes sense to create a multi-select link column in the Books table where multiple authors can be selected.‌

To create a multi-select link column in Cinchy, you simply check off the Multi-Select option when you create a new link column.

PreviousIndexing and PartitioningNextTable and Column GUIDs

Last updated 1 year ago

Was this helpful?

Image 1
Image 2
Image 3
#1.-about-linking
2. Choosing a Linked Column
3. Relationships