How can you avoid challenges when developing databases?

Challenges will always be present, but it is possible to minimize the impact with proper planning; in my personal experience the first thing I do is to inform myself very well of "why" and "what for" a database is being implemented, maybe a spreadsheet is enough in some cases. Planning an adequate data architecture that goes hand in hand with the technological infrastructure and the projection of the organization in the next 5 years, I consider it an important baseline to face challenges throughout the development and implementation of a database.

?? Use Entity Relationship (ER) diagrams: These are visual tools that allow you to map out the various entities in your database and the relationships between them. ER diagrams are invaluable for avoiding redundancy and ensuring data integrity by clarifying how tables are interrelated. ?? Having a well-defined data dictionary from the get-go can eliminate confusion and errors down the line. This is a detailed description of the data that your database will store, including field names, types, and any constraints.

Before writing any code, having a clear understanding of the database’s purpose, the data it will store, and its relationships with other systems is crucial. This approach minimizes the risks of errors, inconsistencies, and redundancies, which are common pitfalls in database development. Documenting assumptions and requirements early on also facilitates better communication among stakeholders and serves as a reference throughout the development process. This disciplined approach not only streamlines the coding phase but also contributes to the database’s long-term scalability and maintainability.

Before picking a database type, understand your project's needs. Are you dealing with structured data that fits well in tables, or is your data more varied and unstructured? Do you need to perform complex joins and queries, or is your priority fast reads and writes? Below, some of the most popular database types: ?? Relational Databases: Best for structured data and complex queries. Examples include MySQL and PostgreSQL. ?? Non-Relational Databases: Suitable for unstructured data and quick development. MongoDB and Firebase's Firestore are examples. ?? Hybrid Databases: These databases, such as Oracle, offer features of both relational and non-relational databases and are suitable for varied data storage needs.

One area of concern is having to replicate data to a vector database for AI purposes. Recently some major names such as PostgreSQL, REDIS and Neo4J have rolled out native vector support. Cloud vendor-specific solutions such as Azure Cosmos DB also offer native vector support. One of the perks of using a cloud vendor database is that it is managed and can generally scale effortlessly, yet there is a price tag attached to that. Database caching in any and all configurations remains a requirement for any performant solution. There is also a fair bit of confusion on a single database, a data lake, a data warehouse, and finally, a data lakehouse. They are not the same things, but some vendors cover all the use cases.

The choice between relational, non-relational, or hybrid databases depends on various factors such as the project’s goals, data characteristics, and the required level of scalability, performance, flexibility, and compatibility. Relational databases like MySQL and PostgreSQL are well-suited for structured data with clear relationships, offering robust ACID (Atomicity, Consistency, Isolation, Durability) compliance, which is essential for applications requiring strong consistency and transactional support. Non-relational databases (NoSQL) like MongoDB and Firebase are advantageous for handling unstructured or semi-structured data, providing greater flexibility in data models.

Adhering to coding standards ensures that your database code is easy to understand, reduces the chance of errors, and makes maintenance more straightforward. A well-organized codebase encourages team collaboration and makes it easier for new developers to get up to speed. ?? Stick to a specific naming pattern for tables, variables, and stored procedures. ?? Choose the most appropriate data types for your fields. It not only enhances data integrity but also improves performance by using only the necessary resources. ?? Make peer reviews a regular practice. This catches issues early and ensures that the code meets the project's coding standards.

Something mandatory from my end is always the same and regardless the RDBMS: put in uppercase the reserved words for a major readability and terminate all the queries with semicolon; at the same time document everything not only for others but for yourself.

Validation is equally important, as it ensures that the data stored and managed by the database is accurate, complete, and consistent. Data profiling involves analyzing the data to uncover patterns, anomalies, and insights, which can inform further improvements to the database design and data management practices. Together, testing and validation provide a robust framework for ensuring that the database not only meets functional requirements but also supports the integrity and reliability of the data it manages. This is crucial for any application that relies on accurate and consistent data, particularly in industries where compliance and data quality are paramount.

Estimated Execution Plan: Offers insight into what SQL Server's query optimizer intends to do. Actual Execution Plan: Shows what SQL Server did when executing the query. Learn to read and understand the cost associated with each operator in the plan. More on Actual Execution Plan: This is a very deep subject, but on a high level, utilize the Actual Execution Plan to test two queries side by side to see which performs better. Focus on 'Query cost (relative to the batch)'. From here, you can dive into what area specifically is causing slowdowns (see the execution path below the query cost, and find the highest percentage items first.)

Avoid the table scan. A poorly designed query may read every row in the table to produce a result set. The trick is in the where clause.

Also, do not forgot to properly index your tables. There is an art to indexing. An index built for a fast select may slow down update and insert. I generally include all the foreign key fields plus where clause fields.

Examine in detail the query estimated plan in order to check everything is in order: index seek rather than index scan opetaror -when possible- and read carefully the hints on missing indexes (not always are necessary). Run the same queries in a minimum 10-20 slot and grab the averages in reads and writes

Integrate with Active Directory (AD) when you can. This will mean loading additional modules to AD to use this functionality. Then use groups within the database environment instead of individual logins. This approach also lends itself to utilizing a smaller security footprint.

Before using NOLOCK, please evaluate your alternatives. Consider using read replicas, snapshot isolation, caching results, or an action queue. NOLOCK is heavily used, but it is generally not recommended. Read about why NOLOCK should be avoided in articles by Brent Ozar, who is a big fan of hating nolock, and others. NOLOCK can lead to incorrect results if data is being modified concurrently. NOLOCK can be a great option if you fully understand its implications. However, it is important to be aware of the potential data inconsistencies before using it.

A quick easy ..add with(no lock) to your views and queries the ones feeding your Olap, reporting, extraction…the ones after a batch job and are not on active/intense transaction. Some will say is not needed and that’s the default behavior. But countless of time I have seen locks and contention from weird complex queries….so I learned my lesson and always put it ;)