database-relations

33 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Polymorphic relations definition

Polymorphic relationships allow a single relationship to point to different types of entities or tables. They create more flexible and adaptable relationships when you have different kinds of data that share some commonality. Instead of creating separate tables and relationships for each type, a polymorphic approach uses a type indicator and an ID that can reference different tables.

Database normalization fundamentals

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves organizing information into logical structures to minimize duplication, prevent inconsistencies, and prevent anomalies including insertion anomalies (difficulty adding new data due to missing related information), update anomalies (having to update the same information in multiple rows), and deletion anomalies (accidentally losing valuable information when deleting something seemingly unrelated). A normalized database is more logically structured and easier to understand, query, and modify.

First Normal Form (1NF) - Atomic Values

First Normal Form requires that each column holds a single, indivisible value with no repeating groups of data within a single cell. For example, an address column storing '123 Main St, City, USA' violates 1NF and should be split into separate columns: street_address, city, state, and zip_code.

Second Normal Form (2NF) - Eliminate Redundant Data Dependent on Part of the Key

Second Normal Form applies to tables with a composite primary key (a primary key made up of two or more columns). 2NF ensures that all non-key attributes are fully dependent on the entire composite primary key, not just part of it. Attributes that are only dependent on a portion of the composite key (partial dependencies) must be removed and placed in a separate table where they are fully dependent on that table's primary key.

Third Normal Form (3NF) - Eliminate Redundant Data Dependent on Non-Key Attributes

Third Normal Form removes data that is dependent on other non-key attributes, eliminating transitive dependencies. If a non-key attribute depends on another non-key attribute, the dependent attributes should be moved to a separate table keyed by that non-key attribute.

One-to-One relationship definition

In a one-to-one relationship, each record in table A is related to at most one record in table B, and each record in table B is related to at most one record in table A. It is a direct, exclusive pairing.

One-to-Many relationship definition

In a one-to-many relationship, one record in table A can be related to many records in table B, but each record in table B is related to at most one record in table A. This is a parent-child relationship.

Many-to-Many relationship definition

In a many-to-many relationship, one record in table A can be related to many records in table B, and one record in table B can be related to many records in table A. This is a bidirectional relationship. Many-to-many relationships are implemented using a junction table (also called an associative table or bridging table) that acts as an intermediary to link records from both tables.

Many-to-Many junction table example

Many-to-many relationships require a junction table. For example, a Students and Courses many-to-many relationship uses an Enrollments junction table with student_id and course_id foreign keys. A UNIQUE constraint on (student_id, course_id) prevents duplicate enrollments for the same student and course.

Foreign key constraints enforce relationships

Foreign key constraints explicitly define and enforce relationships in the database. They tell the database that a column value must correspond to a valid value in the primary key column of another table. The database actively enforces this constraint, making the database relationship-aware.

Foreign keys maintain referential integrity

Referential integrity means that relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records (records in one table that don't have a corresponding record in the related table). For example, without a foreign key constraint, you could delete a customer while their orders still exist in the Orders table, creating orphaned orders. Foreign keys prevent this data inconsistency or allow controlled behavior via CASCADE, SET NULL, etc.

Foreign keys as database design documentation

Foreign keys serve as a crucial part of database design documentation. When you see a foreign key in a database schema, it immediately indicates that one table is related to another table in a specific way. This makes databases easier to understand, maintain, and evolve over time, allowing new developers to quickly grasp how different parts of the database are connected.

Performance considerations for foreign keys

In extremely high-write transactional systems (such as real-time logging or high-frequency trading platforms), foreign key checks can introduce a small but potentially noticeable performance overhead. Every insert or update in a table with a foreign key requires the database system to perform referential integrity checks.

Foreign keys in distributed database systems

Cross-node foreign keys in distributed database systems can introduce significant complexity and performance overhead. Validating referential integrity requires communication between nodes, leading to increased latency. Distributed transactions needed to maintain consistency are more complex and less performant than local transactions. In such architectures, application-level data integrity checks or eventual consistency models might be considered alternatives.

Foreign keys with legacy and non-relational data

When integrating a relational database with legacy systems or non-relational data stores (such as NoSQL, flat files, or external APIs), imposing foreign keys can lead to data import issues and inconsistencies. Legacy systems or non-relational data might not consistently adhere to referential integrity rules. In such scenarios, you might need to rely on application logic or ETL processes to ensure data integrity instead of strictly enforcing foreign keys at the database level.

Polymorphic relations implementation

Polymorphic relationships are typically handled at the application level or using advanced database features, as standard SQL does not have direct, built-in support for enforcing polymorphic foreign key constraints in the same way as regular foreign keys. A polymorphic design typically includes two columns: a type column (e.g., commentable_type) that identifies which table is being referenced, and an ID column (e.g., commentable_id) that stores the primary key of the referenced record.

defineRelations function for PostgreSQL

defineRelations is used to define soft relations between tables in Drizzle ORM. It takes an object with table definitions and a callback function that receives a relation builder (r) object. The relation builder provides r.one for one-to-one relations and r.many for one-to-many relations. Relations are application-level abstractions that do not create foreign keys implicitly and do not affect the database schema.

r.one() relation configuration fields

The r.one() method accepts the following configuration fields: 'from' (specifies the source column for the relation), 'to' (specifies the target column), 'optional' (boolean, false makes the relation required at type level), 'alias' (custom string identifier for disambiguating multiple relations between same tables), 'where' (object for polymorphic relations filtering based on conditions). The key name used (e.g., 'author') is the custom key that appears in the related object when using relational queries.

r.many() relation configuration fields

The r.many() method accepts the following configuration fields: 'from' (specifies the source column), 'to' (specifies the target column), 'alias' (custom string identifier for disambiguating multiple relations), 'where' (object for polymorphic relations filtering). Returns an array of objects from the target table rather than a single object. The key name used (e.g., 'feed') is the custom key that appears in the related object.

One-to-one self-referencing relation example

A user can invite another user by setting up a self-referencing one-to-one relation using defineRelations. Create a users table with an 'invitedBy' foreign key column, then define a relation named 'invitee' using r.one.users({ from: r.users.invitedBy, to: r.users.id }). This creates a self-reference where the invitedBy column points to another user's id.

One-to-one relation with foreign key in target table

When defining a one-to-one relation where the foreign key is in the target table (e.g., profileInfo has userId pointing to users.id), the user relation can have neither fields nor references, making user.profileInfo nullable. The relation is defined as r.one.profileInfo({ from: r.users.id, to: r.profileInfo.userId }), and TypeScript infers the type as { id: number, name: string | null, profileInfo: { ... } | null }.

One-to-many relation example with users and posts

Define a one-to-many relation between users and posts: in posts table add authorId column, then use defineRelations with posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }) } and users: { posts: r.many.posts() }. This allows querying users with their posts or posts with their authors.

Many-to-many relation using junction table and through

Define many-to-many relations using a junction table with r.many and the through() method. For users and groups: define usersToGroups junction table with userId and groupId foreign keys and a composite primary key. In defineRelations, use r.many.groups({ from: r.users.id.through(r.usersToGroups.userId), to: r.groups.id.through(r.usersToGroups.groupId) }). The through() method bypasses junction table selection and directly selects related entities.

Predefined where filters in relations

Relations support predefined where statements for polymorphic relations. When defining a relation like r.many.users({ from: ..., to: ..., where: { verified: true } }), only users with verified=true will be retrieved. Filters can only be specified on the target (to) table, not the source (from) table.

defineRelationsPart for separating relation configurations

Use defineRelationsPart to separate relation configurations into multiple parts. Pass main relations first to the db instance: drizzle({ relations: { ...relations, ...part } }). Order matters because the main relations recursively infer all table names for autocomplete. If only using parts, one part must be empty (defineRelationsPart(schema)) to ensure all tables are inferred.

Relations vs foreign keys in Drizzle

Foreign keys are database-level constraints checked on every insert/update/delete operation. Relations are application-level abstractions that do not affect the database schema and do not create foreign keys implicitly. Relations and foreign keys can be used together or independently, allowing relations to work with databases that do not support foreign keys.

Indexing strategy for one-to-one relationships

For one-to-one relationships, create an index on the foreign key column in the target table being referenced. This optimizes JOIN operations by allowing the database to quickly locate related rows. For example, create an index on userId in the profile_info table when querying users with their profile information.

Indexing strategy for one-to-many relationships

For one-to-many relationships, create an index on the foreign key column in the table representing the 'many' side (the table with the foreign key). For example, index the authorId column in the posts table to optimize queries fetching users with their posts or posts with their authors.

Indexing strategy for many-to-many relationships

For many-to-many relationships using junction tables, create three indexes: an index on each foreign key column individually (userId and groupId) and a composite index on both columns together. Individual indexes optimize queries filtering on one side of the relationship. The composite index is crucial for efficiently resolving the many-to-many relationship itself when Drizzle needs to find connections between both entities.

Using alias to disambiguate multiple relations

When defining multiple relations between the same two tables, use the alias option to disambiguate them. For example, a posts table with both author and reviewer relations to users: define author relation with alias: 'author' and reviewer relation with alias: 'reviewer'. The alias string provides a unique identifier for each relation.

One-to-one relation index example code

Example of adding an index to optimize one-to-one relations: ```typescript export const profileInfo = p.pgTable('profile_info', { id: p.integer().primaryKey(), userId: p.integer('user_id').references(() => users.id), metadata: p.jsonb(), }, (table) => [ p.index('profile_info_user_id_idx').on(table.userId) ]); ``` This creates an index on userId to optimize JOIN operations.

One-to-many relation index example code

Example of adding an index to optimize one-to-many relations: ```typescript export const posts = p.pgTable('posts', { id: p.integer().primaryKey(), content: p.text(), authorId: p.integer('author_id'), }, (t) => [ p.index('posts_author_id_idx').on(t.authorId) ]); ``` This creates an index on authorId in the 'many' side table.

Many-to-many junction table index example code

Example of adding indexes to optimize many-to-many relations: ```typescript export const usersToGroups = p.pgTable( 'users_to_groups', { userId: p.integer('user_id').notNull().references(() => users.id), groupId: p.integer('group_id').notNull().references(() => groups.id), }, (t) => [ p.primaryKey({ columns: [t.userId, t.groupId] }), p.index('users_to_groups_user_id_idx').on(t.userId), p.index('users_to_groups_group_id_idx').on(t.groupId), p.index('users_to_groups_composite_idx').on(t.userId, t.groupId), ], ); ``` Creates individual indexes on each foreign key and a composite index on both.

Give your agent this brain