Using Database Level Associations v/s Active Record Associations in Ruby On Rails

What are database level associations?

Database level associations refers to relationships between tables in a database that are established through foreign keys. These relationships are defined and managed directly in the database schema.
For example - Suppose you have two tables in your database, 'users' and 'posts', where each user can have multiple posts. So to establish a one-to-many relationship between these two tables, you can create a foreign key constraint in the 'posts' table that references the 'id' column in the 'users' table.

What are active record associations?

Associations in rails allow you to specify relationships between your models and their related data in database. They are defined using ActiveRecord and are used to simplify the process of working with related data.
For example - Suppose you have two models in you application, 'User' and 'Post', where each user can have multiple posts. To define their relationship you would add the following code to your 'User' model:

class User < ApplicationRecord
  has_many :posts
end

and the following code to your 'Post' model:

class Post < ApplicationRecord
  belongs_to :user
end

Using database level associations

Using database level associations has it's own advantage which make a huge difference

Performance: It can be faster than using ActiveRecord associations, especially in complex, multi-layer associations.

Fine-grained control: It allow you to have fine-grained control over your database interactions, giving you the ability to optimise your database queries and interactions to meet the specific requirements of your application.

Simplicity: By avoiding the overhead of the ActiveRecord association abstractions, database-level associations can make your code simpler and easier to understand.

Increased flexibility: With database-level associations, you are not limited to the abstractions provided by the framework and can implement custom relationships between your tables if needed.

Scalability: In large, high-performance applications, database-level associations can be more scalable than using ActiveRecord associations, especially in terms of memory usage and query performance.

Apart from these benefits there are some drawbacks also, such as

Complexity- Increased complexity by directly using database level associations.

Schema Changes - The need to change relationships directly in the database schema.

Maintenance - It can make your code less maintainable because they are not as abstracted as ActiveRecord associations. You would have to write more raw SQL code, which can be error-prone.

Portability: If you use database-level associations, it might be harder to switch to a different ORM (Object-Relational Mapping) framework or database in the future. ActiveRecord associations, on the other hand, provide a higher level of abstraction and are more portable.

Ultimately, the choice between database-level associations and ActiveRecord associations will depend on the specific requirements of your application, such as performance, scalability, and maintainability.