Relationships

What are database relationships?

Database relationships are crucial connections that link pieces of data across different tables in a database. Imagine it like a web of information where each piece is connected in a logical way.

For example, consider a library: each book has one unique ISBN, which is similar to a one-to-one relationship. Many books can belong to one category, which reflects a one-to-many relationship. Finally, readers may borrow many books, and many copies of a book can be borrowed by different readers, representing a many-to-many relationship. These connections help in organizing data efficiently, allowing more straightforward retrieval and management of the information stored.

Typically, when discussing database relationships, you'll see two terms that are important to remember:

• Primary Key - The primary key inside of a database table is the key that's used to maintain uniqueness between records — a value that's always different for each record. Usually, this is an id field.

• Foreign Key - The foreign key inside of a database table refers to a field that references a primary key inside of another table. For example, a table of books might have a field that contains foreign keys from an authors table.

Types of Relationships

In Xano, there are three primary ways tables can be related:

1. One-to-One

This is like a person and their unique passport. Each data entry in one table relates to exactly one entry in another table.

2. One-to-Many

Think of a parent and their children. A single entry in one table can relate to multiple entries in another. For example, one teacher can teach many students.

3. Many-to-Many

Similar to students enrolling in various courses, any entry in one table can relate to multiple entries in another.

Why Use Relationships?

• Data Consistency: Ensures all references are valid.

• Reduced Redundancy: Minimizes repeated data.

• Efficient Data Retrieval: Makes it easier to access related data.

Understanding these basic concepts can simplify how you view databases and highlight why tools like Xano are powerful for managing data.

Using the Table Reference Field Type

When you add a table reference field to a database table, that field simply stores the IDs of the record(s) being referenced; the data is not actually duplicated. To access the actual data is typically done via an add-on as part of a function stack.

Auto-Complete

Auto-Complete allows you to configure how the referenced records look inside of other tables.

For this example, we have two tables: user and userRole

Relationship View

You can quickly observe all of your table relationships (that utilize the table reference field to contain the foreign keys of other records) by selecting the "Show table relationships" option when viewing your database tables.

From this view, you can visualize all of your table relationships, and click/drag the tables to organize the view based on your preferences.

^{Please note that customization in this view are not saved.}