TIL: Associative Table and Many-to-Many Relationship

There are many terms calling this as discussed in this stackoverflow, and even in this Wikipedia page:

Associative tables are colloquially known under many names, including association table, bridge table, cross-reference table, crosswalk, intermediary table, intersection table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, pivot table (as used incorrectly in Laravel—not to be confused with the correct use of pivot table in spreadsheets), or transition table.
https://en.wikipedia.org/wiki/Associative_entity

It’s even more interesting that http://en.wikipedia.org/wiki/Junction_table is being redirected to https://en.wikipedia.org/wiki/Associative_entity.

About what it is:

In database design, associative tables are utilized to manage many-to-many relationships between entities. When two entities have a relationship where one instance of the first entity can be associated with multiple instances of the second entity, and vice versa, an associative table is necessary to manage this relationship.

For example, in a library database, a book can be written by multiple authors, and an author can write multiple books. To represent this many-to-many relationship, an associative table, often called a junction table, is used. This table typically contains the primary keys of the two entities it is connecting (in this case, books and authors).

The associative table helps in avoiding data redundancy and ensures efficient management of the many-to-many relationship without the need for complex hierarchical structures or duplicating data. It forms a crucial element in relational database design, enabling the establishment and maintenance of robust associations between entities.
Explained by AI.

TIL – MySQL Data Type DECIMAL

This data type comes up when we design a column to store money amount. We’d like to have accuracy and avoid the funny famous issue in programming 0.1 + 0.2 !== 0.3.

The MySQL data type DECIMAL (with NUMERIC as its alias) is used for storing decimal numbers with fixed precision and scale. It is suitable for dealing with precise numeric values where accuracy is crucial, such as monetary amounts.

Continue reading →

TIL – MySQL ON DUPLICATE KEY UPDATE

// Start written by AI ChatGPT

The ON DUPLICATE KEY UPDATE statement in MySQL is used when you want to either insert a new row into a table or update the existing row if a duplicate key constraint is violated. In other words, it allows you to handle duplicates gracefully.

Continue reading →

TIL – It’s more clear Data Mapper – Active Record, ORM?

Active Record is a design pattern where the model is responsible for both the data and the behavior related to that data, allowing direct interaction with the database through the model itself. In contrast, the Data Mapper pattern separates the in-memory objects from the database, using a repository to manage data access, which is more suitable for larger applications. There is a pretty good explanation here https://typeorm.io/active-record-data-mapper/

Some examples regarding these patterns:

For WooPayments server, we use Data Mapper: Repository (all database actions are here) <> Model (Entity). It’s actually good for the large applications like it.
Laravel, by default, uses the Active Record pattern as its models interact directly with database. But it’s also possible to use Data Mapper pattern in Laravel.

ORM (Object-Relational Mapping) is a programming technique that enables developers to interact with relational databases using high-level object-oriented programming languages, abstracting the complexities of SQL. By mapping database tables to programming objects, ORM allows for seamless data manipulation through objects rather than cumbersome queries. This approach enhances productivity, increases code readability, and helps prevent security issues like SQL injection. The “Patterns of Enterprise Application Architecture” book by Martin Folwer is a good source for this.