Relationship fields in Odoo establish connections between different database tables. These relationships rely on unique identifiers (IDs) that serve as primary keys in each table. The ID's uniqueness enables efficient data retrieval through clustered indexing, alllowing rapid query performance regardless of dataset size.

Odoo provides three primary relationship field types:

• Many2one: Creates a field in the primary table that stores the ID of a related record from another table. Each primary record can reference only one secondary record. For example, a book record can have only one publisher.
• One2many: The inverse of Many2one, where a primary record can reference multiple secondary records. When used with Many2one, these fields enable bidirectional data access. Odoo creates a view to manage this relationship.
• Many2many: Uses an intermediate table to store relationships between primary and secondary records. This allows multiple associations in both directions, but unlike One2many, the relationship is unidirectional unless explicitly defined.

class BookClassification(models.Model):
    _name = 'library.book.classification'
    
    _parent_store = True
    _parent_name = "classification_parent"

    classification_name = fields.Char('Classification')
    
    # Many2one field
    classification_parent = fields.Many2one(
        'library.book.classification',
        string='Parent Classification',
        ondelete='restrict',
        index=True
    )
    
    # One2many field
    classification_children = fields.One2many(
        'library.book.classification', 'classification_parent',
        string='Child Classifications'
    )
    
    # Many2many field with default values and domain
    def _get_default_writers(self):
        return [(6, 0, [self.env.user.id])]
    
    def _filter_writers(self):
        return [('company_id', '=', self.env.user.company_id.id)]
    
    book_writers = fields.Many2many('res.partner',
        'book_writer_relation', 'book_ref', 'writer_ref',
        string='Writers',
        default=_get_default_writers,
        readonly=True,
        copy=False,
        states={'draft': [('readonly', False)], 'confirmed': [('readonly', False)]},
        domain=_filter_writers
    )
    
    classification_path = fields.Char(index=True)
    
    @api.constrains('classification_parent')
    def _validate_hierarchy(self):
        if not self.classification_parent._check_recursion():
            raise models.ValidationError('Hierarchy recursion detected in classifications.')

Many2many fields can specify custom relation table names:

# Many2many with custom relation table
access_rights = fields.Many2many('res.groups', 
    'book_access_rights_rel', 'group_ref', 'right_ref', 
    string="Access Rights", 
    domain=[('name', 'ilike', 'Book')]
)

Creating records with relationship fields:

# Creating records with relationship field values
# Many2one: Use the related record ID
# One2many and Many2many: Use list of tuples
self.env['library.book'].create({
    'title': 'Advanced Python',
    'book_writers': [
        (0, 0, {'name': 'John Smith'}),
        (0, 0, {'name': 'Jane Doe'}),
        (6, 0, [1, 2, 3])
    ]
})

Updating relationship fields:

# Updating One2many and Many2many fields
# Values are lists of tuples with specific operations:
# (0, 0, values_dict) - Create new record and link
# (1, id, values_dict) - Update linked record
# (2, id) - Unlink and delete record
# (3, id) - Unlink without deleting record
# (4, id) - Link existing record
# (5, 0) - Unlink all records
# (6, 0, id_list) - Replace all links with new IDs

Important: Always use Odoo's ORM methods for data updates rather than direct SQL queries. Odoo employs extensive caching mechanisms, and direct database modifications can cause data synchronization issues. For instance, permission changes made via SQL might not take effect until service restart due to cached permission data.