rstriquer

Artificial Intelligence has dramatically accelerated software development. However, after using LLMs in several real-world projects, I noticed that the largest challenge was no longer generating code, it was maintaining engineering discipline. Modern software projects require much more than implementation. Every feature introduces architectural decisions, security implications, performance considerations, operational costs, and long-term maintenance challenges (to mention some). Asking a single AI assistant to handle all these concerns simultaneously often produces inconsistent results. This observation motivated me to build an AI-assisted engineering workflow that separates software development into specialized responsibilities instead of relying on one generic prompt. The Motivation One of my ongoing projects is a production-grade link curation platform lnk4st.co ( https://lnk4st.co ) inspired by services such as "Link Tree" and "link Bio". Although the platform ...

Have you ever wondered why your database sorts strings differently than expected, or why a seemingly simple query delivers "quirky" results? If you’ve worked with databases like MySQL, PostgreSQL, or SQL Server, you’ve likely stumbled across the mysterious world of collation and the subtle but critical differences between VARBINARY and VARCHAR data types. In this article I hope to unravel these concepts for you, explore how collation shapes database behavior, and dive into an experimental case study to reveal surprising insights. Whether you’re a developer or a database engineer, this deep dive will equip you with practical knowledge to avoid common pitfalls and optimize your database designs. Let’s get started! What Is Collation and Why Does It Matter? Collation refers to the set of rules a database uses to compare and sort character strings. It governs how strings are ordered (e.g., is “Apple” less than “apple”?), how accents are treated (e.g., does “é” equal “e”?), and eve...

Learn how deadlocks occur in database systems, understand their impact on performance, and discover practical techniques for identifying potential deadlock scenarios in your SQL code.

  Demand ALWAYS name unique key as "uq_{IndexName}" Description We use this convention to easily identify the source of the failure, especially in schema updates. When creating a CONSTRAINT we create its name starting with the string "uq", followed by the full name of the source table column used to build the index. Both separated by "_" (underline). Examples 1: CREATE TABLE customers ( 2:     id INT NOT NULL, 3:   name VARCHAR(100) NOT NULL, 4:    user_id INT NULL COMMENT 'if the customer have a system login it will be refernced here', 5:      PRIMARY KEY (id), 6: UNIQUE KEY `uq_user_id` (`user_id`), 6:      CONSTRAINT `fk_customers_user_id_users_user_id` 7:           FOREIGN KEY (`user_id`) 8:      REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 9: ); Examples Explanation Here the  uq_user_id  is used to build a unique key...

Demand ALWAYS name forein keys constraints as "fk_{LocalTableName}_{LocalColumnName}_{DestinationTableName}_{DestinationFieldName}" Description We use this convention to easily identify the source of the failure, especially in schema updates. When creating a CONSTRAINT we create its name starting with the string "fk", followed by the full name of the source table, also called local table or child table, followed by the name of the column used in the source table to store the value to be searched for later in the target table, the name of the target table and finally the name of the field in the target table. All separated by "_" (underline). Examples 1: CREATE TABLE customers ( 2:     id INT NOT NULL, 3:   name VARCHAR(100) NOT NULL, 4:    user_id INT NULL COMMENT 'if the customer have a system login it will be refernced here', 5:      PRIMARY KEY (id), 6:      CONSTRAINT `fk_customers_user_id_users_user_id` 7:...

Demand Use "id_{context}" to name fields of external identification codes and "{context}_id" to internal ones. Description When creating a column in a table that reference an id (identification code) of a table located at the database you are dealing it should be named as "{Table Name}_id". When creating a column in a table that reference an id of a context external to the database you are dealing with  it should be named as "id_{Context Name}". Examples 1: CREATE TABLE customer ( 2:     id INT NOT NULL, 3:   name VARCHAR(100) NOT NULL, 4:     id_ssn COMMENT ' the United States, the Social Security number', 5:    user_id INT NULL COMMENT 'if the customer have a system login it will be refernced here', 6:      PRIMARY KEY (id), 7:     UNIQUE `uq_ssn` (`ssn`), 8:      CONSTRAINT `fk_customers_user_id_users_user_id` 9:           FOREIGN KEY (`user...

Demand MUST use "=" (equals sign) instead "LIKE" (like command) whenever "%" (percent sign) is NOT necessary Description In MySQL, the " LIKE " operator behaves similarly to " = " (equals sign) when the " % " (percent sign) wildcard is not utilized. Employing " LIKE " in most of the modern SGDBs (e.q. MySQL) results in the use of a more complex query structures. The examples below illustrate this. The first query does not use the " % " modifier and is compared to the second, which does. Both provide the same search structure. Contrasting these with the last query, which uses only " = ", results in a simpler search structure and all have the same output result. Therefore, if the " % " modifier is not being used in the search, it's preferable to utilize " = " for simpler and more efficient query execution. Examples 1: explain SELECT COUNT(1) FROM users WHERE `username` lik...