Database Relationships
3 minutes to readRelationships rebuild normalized data from multiple tables. In relational databases such as Caspio, it is a best practice to normalize the data.
Normalization is a process by which large tables are divided into smaller tables and relationships are defined between them. It has two benefits:
- Eliminates redundant data (i.e., repeating the same data in multiple records).
- Improves the performance of your database.
For example, when managing customers and their orders, it is inefficient to repeat customer data with every order. Upon normalization, two tables are created, one for customers (parent table) and another for orders (child table). A primary ID in the parent table and its corresponding foreign ID in the child table are used to form a relationship between the two tables.
By configuring relationships between related tables, you can essentially work with them as if they were a single table. You can perform cross-table queries or create views—where Caspio auto-detects certain view settings such as join fields and join type—all without sacrificing normalization.
Relationship Types
There are two types of relationships in Caspio:
- A one-to-one relationship is used when each record in the first table has only one related record in the second table. This relationship is rarely used because it is usually more efficient to put all of the information in a single table. When creating a relationship between two tables, if both fields used in the relationship are unique, a one-to-one relationship is automatically set.
- A one-to-many relationship is the most common type of database relationship. It occurs when each record in the parent table relates to one or more records in the child table, but each record in the child table only relates to one record in the parent table. For example, the relationship between the Customers table and Orders table is a one-to-many because each customer may place multiple orders but each order only belongs to one customer. When creating a relationship between two tables, if only one of the related fields is unique, a one-to-many relationship is automatically set.
Other Relationships
- A many-to-many relationship occurs when each record in table A relates to multiple records in table B and each record in table B relates to multiple records in table A. For example, the relationship between the Products table and Orders table is a many-to-many because one order can have many products and each product can appear in many orders. Caspio is similar to most major database management systems where many-to-many relationships are possible via a third table. In our example, the Orders_Products table is the third table, with two one-to-many relationships with the Orders and Products tables.
- An indeterminate relationship occurs when neither of the fields in a relationship is unique. Referential integrity and cascading update/delete are not available for indeterminate relationships.
Compatible Data Types
Relationships are made by associating a field from one table to a field from another table. The fields used in relationships must be of compatible data types, otherwise a relationship cannot be formed. The table below shows relationship compatibility among data types.
| Data Types | Relationship Compatibility |
| Integer | Integer, Autonumber |
| Autonumber | Autonumber, Integer |
| Number | Number |
| Currency | Currency |
| Text (255) | Text (255), Prefixed Autonumber, Random ID, GUID |
| Prefixed Autonumber | Prefixed Autonumber, Text (255) |
| Random ID | Random ID, Text (255) |
| GUID | GUID, Text (255) |
| Date/Time | Date/Time, Timestamp |
| Timestamp | Timestamp, Date/Time |
Note: Password, Yes/No, File, List, Formula and Text (64000) data type fields are not available in relationships and are not shown in these interfaces.
For more details, you can watch the recording of our live session:
Read the following articles to learn more about database relationships:
