by Joche Ojeda | Jun 21, 2024 | Database, ORM
Why Compound Keys in Database Tables Are No Longer Valid
Introduction
In the realm of database design, compound keys were once a staple, largely driven by the need to adhere to normalization forms. However, the evolving landscape of technology and data management calls into question the continued relevance of these multi-attribute keys. This article explores the reasons why compound keys may no longer be the best choice and suggests a shift towards simpler, more maintainable alternatives like object identifiers (OIDs).
The Case Against Compound Keys
Complexity in Database Design
- Normalization Overhead: Historically, compound keys were used to satisfy normalization requirements, ensuring minimal redundancy and dependency. While normalization is still important, the rigidity it imposes can lead to overly complex database schemas.
- Business Logic Encapsulation: When compound keys include business logic, they can create dependencies that complicate data integrity and maintenance. Changes in business rules often necessitate schema alterations, which can be cumbersome.
Maintenance Challenges
- Data Integrity Issues: Compound keys can introduce challenges in maintaining data integrity, especially in large and complex databases. Ensuring the uniqueness and consistency of multi-attribute keys can be error-prone.
- Performance Concerns: Queries involving compound keys can become less efficient, as indexing and searching across multiple columns can be more resource-intensive compared to single-column keys.
The Shift Towards Object Identifiers (OIDs)
Simplified Design
- Single Attribute Keys: Using OIDs as primary keys simplifies the schema. Each row can be uniquely identified by a single attribute, making the design more straightforward and easier to understand.
- Decoupling Business Logic: OIDs help in decoupling the business logic from the database schema. Changes in business rules do not necessitate changes in the primary key structure, enhancing flexibility.
Easier Maintenance
- Improved Data Integrity: With a single attribute as the primary key, maintaining data integrity becomes more manageable. The likelihood of key conflicts is reduced, simplifying the validation process.
- Performance Optimization: OIDs allow for more efficient indexing and query performance. Searching and sorting operations are faster and less resource-intensive, improving overall database performance.
Revisiting Normalization
Historical Context
- Storage Constraints: Normalization rules were developed when data storage was expensive and limited. Reducing redundancy and optimizing storage was paramount.
- Modern Storage Solutions: Today, storage is relatively cheap and abundant. The strict adherence to normalization may not be as critical as it once was.
Balancing Act
- De-normalization for Performance: In modern databases, a balance between normalization and de-normalization can be beneficial. De-normalization can improve performance and simplify query design without significantly increasing storage costs.
- Practical Normalization: Applying normalization principles should be driven by practical needs rather than strict adherence to theoretical models. The goal is to achieve a design that is both efficient and maintainable.
ORM Design Preferences
Object-Relational Mappers (ORMs)
- Design with OIDs in Mind: Many ORMs, such as XPO from DevExpress, were originally designed to work with OIDs rather than compound keys. This preference simplifies database interaction and enhances compatibility with object-oriented programming paradigms.
- Support for Compound Keys: Although these ORMs support compound keys, their architecture and default behavior often favor the use of single-column OIDs, highlighting the practical advantages of simpler key structures in modern application development.
Conclusion
The use of compound keys in database tables, driven by the need to fulfill normalization forms, may no longer be the best practice in modern database design. Simplifying schemas with object identifiers can enhance maintainability, improve performance, and decouple business logic from the database structure. As storage becomes less of a constraint, a pragmatic approach to normalization, balancing performance and data integrity, becomes increasingly important. Embracing these changes, along with leveraging ORM tools designed with OIDs in mind, can lead to more robust, flexible, and efficient database systems.
by Joche Ojeda | May 29, 2023 | Uncategorized
SOLID is an acronym that stands for five fundamental principles of object-oriented programming and design. These principles were first introduced by Robert C. Martin (also known as Uncle Bob) and have since become a cornerstone of software development best practices. Each letter in SOLID represents a principle that, when applied correctly, leads to more maintainable and modular code.
Let’s dive into each of the SOLID principles and understand how they contribute to building high-quality software systems:
Single Responsibility Principle (SRP):
The SRP states that a class should have only one reason to change. In other words, a class should have a single responsibility or a single job. This principle encourages developers to break down complex systems into smaller, cohesive modules. By ensuring that each class has a focused responsibility, it becomes easier to understand, test, and modify the code without affecting other parts of the system.
Open-Closed Principle (OCP):
The OCP promotes the idea that software entities (classes, modules, functions, etc.) should be open for extension but closed for modification. This principle emphasizes the importance of designing systems that can be easily extended with new functionality without modifying existing code. By relying on abstractions, interfaces, and inheritance, developers can add new features by writing new code rather than changing the existing one. This approach reduces the risk of introducing bugs or unintended side effects.
Liskov Substitution Principle (LSP):
The LSP states that objects of a superclass should be replaceable with objects of its subclasses without affecting the correctness of the program. In simpler terms, if a class is a subtype of another class, it should be able to be used interchangeably with its parent class without causing any issues. This principle ensures that inheritance hierarchies are well-designed, avoiding situations where subclass behavior contradicts or breaks the functionality defined by the superclass. Adhering to the LSP leads to more flexible and reusable code.
Interface Segregation Principle (ISP):
The ISP advises developers to design interfaces that are specific to the needs of the clients that use them. It suggests that clients should not be forced to depend on interfaces they don’t use. By creating small and focused interfaces, rather than large and monolithic ones, the ISP enables clients to be decoupled from unnecessary dependencies. This principle enhances modularity, testability, and maintainability, as changes to one part of the system are less likely to impact other parts.
Dependency Inversion Principle (DIP):
The DIP encourages high-level modules to depend on abstractions rather than concrete implementations. It states that high-level modules should not depend on low-level modules; both should depend on abstractions. This principle promotes loose coupling between components, making it easier to substitute or modify dependencies without affecting the overall system. By relying on interfaces or abstract classes, the DIP facilitates extensibility, testability, and the ability to adapt to changing requirements.
By applying the SOLID principles, software engineers can create codebases that are modular, flexible, and easy to maintain. These principles provide a roadmap for designing systems that are resilient to change, promote code reusability, and improve collaboration among development teams. SOLID principles are not strict rules but rather guidelines that help developers make informed design decisions and create high-quality software systems.
It’s worth mentioning that the SOLID principles should not be applied blindly in all situations. Context matters, and there may be scenarios where strict adherence to one principle might not be the best approach. However, understanding and incorporating these principles into the software design process can significantly improve the overall quality of the codebase.
SOLID and XPO
XPO is designed with SOLID design principles in mind. Here’s how XPO applies each of the SOLID principles:
Single Responsibility Principle (SRP)
XPO uses separate classes for each major concern such as mapping, persistence, connection providers, and data access. Each class has a clearly defined purpose and responsibility.
Open-Closed Principle (OCP)
XPO is extensible and customizable, allowing you to create your own classes and derive them from the XPO base classes. XPO also provides a range of extension points and hooks to allow for customization and extension without modifying the core XPO code.
Liskov Substitution Principle (LSP)
XPO follows this principle by providing a uniform API that works with all persistent objects, regardless of their concrete implementation. This allows you to write code that works with any persistent object, without having to worry about the specific implementation details.
Interface Segregation Principle (ISP)
XPO provides a number of interfaces that define specific aspects of its behavior, allowing clients to use only the interfaces they need. This reduces the coupling between the clients and the XPO library.
Dependency Inversion Principle (DIP)
XPO was developed prior to the widespread popularity of Dependency Injection (DI) patterns and frameworks.
As a result, XPO does not incorporate DI as a built-in feature. However, this does not mean that XPO cannot be used in conjunction with DI. While XPO itself does not provide direct support for DI, you can still integrate it with popular DI containers or frameworks, such as the .NET Core DI container or any other one.
By integrating XPO with a DI container, you can leverage the benefits of DI principles in your application while utilizing XPO’s capabilities for database access and mapping. The DI container can handle the creation and management of XPO-related objects, facilitating loose coupling, improved modularity, and simplified testing.
A clear example is the XPO Extensions for ASP.NET Core DI:
using DevExpress.Xpo.DB;
Using Microsoft.Extensions.DependencyInjection; // ...
var builder = WebApplication.CreateBuilder(args); builder.Services.AddXpoDefaultUnitOfWork(true, options => options.UseConnectionString(builder.Configuration.GetConnectionString("MSSqlServer")) .UseAutoCreationOption(AutoCreateOption.DatabaseAndSchema) .UseEntityTypes(new Type[] { typeof(User) }));
More information about XPO and DI here: https://docs.devexpress.com/XPO/403009/connect-to-a-data-store/xpo-extensions-for-aspnet-core-di
And that’s all for this post, until next time ))
We are excited to announce that we are currently in the process of writing a comprehensive book about DevExpress XPO. As we work on this project, we believe it is essential to involve our readers and gather their valuable feedback. Therefore, we have decided to share articles from the book as we complete them, giving you an opportunity to provide input and suggestions that we can consider for inclusion in the final release. Keep in mind that the content presented is subject to change. We greatly appreciate your participation in this collaborative effort.
Related Articles
What is an O.R.M (Object-Relational Mapping)
ADO The origin of data access in .NET
Relational database systems: the holy grail of data
by Joche Ojeda | May 24, 2023 | XPO
.NET communicates with a database using ADO.NET, which provides a set of classes and interfaces for accessing and manipulating data stored in a database.
ADO.NET supports various database systems, including relational databases and XML databases.
ADO.NET uses the following protocols to communicate with a database:
1. OLE DB (Object Linking and Embedding, Database) – This is a low-level, component-based API that provides a generic interface for accessing various types of data sources, including relational databases, spreadsheet data, and more.
2. ODBC (Open Database Connectivity) – This is a widely used API for accessing relational databases. It provides a standardized interface for accessing data, regardless of the underlying database management system.
3. ADO.NET Provider – This is a high-level, .NET-based API for accessing data stored in a specific database management system, such as Microsoft SQL Server, Oracle, or MySQL.
The choice of protocol used by ADO.NET to communicate with a database depends on the specific requirements of the application and the database management system being used.
The ADO.NET provider approach is the most common and provides a flexible and scalable solution for accessing and manipulating data stored in a database, supporting a wide range of database systems and communication protocols.
ADO.NET Main Components
The main classes in ADO.NET include:
1. Connection – Represents a connection to a database, providing methods for opening and closing a connection and for executing commands against the database.
2. Command – Represents a database command, such as a SELECT, INSERT, UPDATE, or DELETE statement, and provides methods for executing the command and for retrieving the results of the command.
3. DataReader – Provides a forward-only, read-only view of the results of a command. The DataReader is optimized for retrieving large amounts of data from a database, as it retrieves data in a streaming manner, rather than retrieving all data into memory at once.
4. DataAdapter – Represents a set of data commands and a database connection that are used to fill a DataSet and to resolve changes made to the data back to the database. The DataAdapter is often used in combination with a DataSet to provide a flexible and scalable way to access and manipulate data stored in a database.
5. DataSet – Represents an in-memory cache of data, providing a disconnected view of data that can be used to work with data independently of a database connection. The DataSet provides a rich set of features for manipulating and querying data, including support for relationships between tables, constraints, and transactions.
6. Parameter – Represents a parameter for a database command, providing a way to specify input values for a command, such as the values for parameters in a stored procedure.
These are the main classes in ADO.NET, and they provide a comprehensive and flexible set of tools for accessing and manipulating data stored in a database.
Design problems in ADO.NET 1
ADO.NET 1 refers to the first version of ADO.NET, which was introduced in .NET Framework 1.0, released in 2002. In this version of ADO.NET, data access was performed using direct methods and properties of the various ADO.NET classes, such as SqlConnection and SqlCommand for SQL Server.
The main design problem for ADO.NET 1 is that it requires developers to write database-specific code for each database management system that they wanted to access.
ADO.NET 2 and the DbProviderFactory
With the introduction of .NET Framework 2.0 in 2005, the ADO.NET DbProviderFactory was introduced.
The DbProviderFactory abstract class provides a set of methods for creating database-specific implementations of various ADO.NET classes, such as DbConnection, DbCommand, and DbDataAdapter, and makes it easier for developers to write database-agnostic code.
ADO.NET DbProviderFactory is a factory pattern that provides a standard way to create instances of database-specific classes, such as connection and command classes, in ADO.NET.
The DbProviderFactory is used by the ADO.NET data providers, such as the SqlClient data provider for SQL Server, the OleDb data provider for OLE DB data sources, and the ODBC data provider for ODBC data sources, to provide a common way to create instances of the classes that they implement. This makes it easier for developers to switch between different data providers and to write database-agnostic code that can work with different databases without modification.
The introduction of DbProviderFactory in ADO.NET was an exceptional milestone that revolutionized database access in .NET. By providing a standardized interface for creating database-specific connection objects, DbProviderFactory enhanced the flexibility and portability of data access code. Its ingenious design allowed developers to write data access logic without being tightly coupled to a specific database provider, thereby promoting code reusability and adaptability. This breakthrough was the steppingstone to the emergence and widespread adoption of Object-Relational Mapping Systems (ORMs).
ORMs leveraged DbProviderFactory’s capabilities to abstract the complexities of database interactions and map database entities to object-oriented representations seamlessly. As a result, developers could focus more on business logic and application development rather than dealing with low-level data access intricacies.
The symbiotic relationship between DbProviderFactory and ORMs continues to shape modern software development, empowering developers with powerful tools to efficiently manage and manipulate data in a database-agnostic manner.
Until next time ))
We are excited to announce that we are currently in the process of writing a comprehensive book about DevExpress XPO. As we work on this project, we believe it is essential to involve our readers and gather their valuable feedback. Therefore, we have decided to share articles from the book as we complete them, giving you an opportunity to provide input and suggestions that we can consider for inclusion in the final release. Keep in mind that the content presented is subject to change. We greatly appreciate your participation in this collaborative effort.
Related Articles
Relational database systems: the holy grail of data
by Joche Ojeda | May 15, 2023 | Uncategorized
Last Friday, I received a message from a dear friend and colleague, Pedro Hernandez. He asked me if I had the latest compiled version of the XPO import framework we created in our office. As it turned out, I did not have it readily available and had to search extensively for it.
While conducting this search across my computers, repositories, and virtual machines, I was inspired to create another import framework — yes, another piece of code to maintain.
Most of the time, my research projects begin in the same manner, typically after a conversation with my close friend Jose Javier Columbie. As always, he would say something like this: “Jose, do you think this can be possible? Why don’t you give it a try? If we succeed, that will be el batazo (like hitting a home run).”
In this specific case, that conversation didn’t happen. However, I could hear his words echoing in my mind.
Furthermore, I want this project to be community-driven, not just a technical experiment I have to maintain alone. I truly believe in the power of a community.
First Step
So, let’s begin. I’ll start with my favorite part: naming the project. I spent all weekend pondering this, attempting to condense the concept and associate it with a literary term or a Latin word (these are my preferred methods for naming a project).
Let’s define what the ultimate goal is. In an import process, the aim is to take information from a source ‘A’, translate or transform the information, and then store it in a target ‘B’.
Growing up, I was an avid reader – and I mean, I read a lot. (Now, I’ve switched to audiobooks.) Therefore, after defining what this project is about, naming it became incredibly easy.
noun
noun: alchemy
- the medieval forerunner of chemistry, concerned with the transmutation of matter, in particular with attempts to convert base metals into gold or find a universal elixir.
“occult sciences, such as alchemy and astrology”
Origin
late Middle English: via Old French and medieval Latin from Arabic al-kīmiyā’, from al ‘the’ + kīmiyā’ (from Greek khēmia, khēmeia ‘art of transmuting metals’).
The alchemy Framework
Alchemy is a framework created for DotNet, designed to import data from a data source to a data target. These sources and targets can be anything from a text file, CSV file, a database, ORMs, and so on.
The framework consists of a set of contracts, interfaces, and base classes. When implemented, these allow you to import and transform data between various sources and targets.
The requirement in a few words
As stated in the framework’s description, the requirement is only to define the contracts that represent the sources and targets, as well as a job configuration that describes how the information flows from one source to a target. The concrete implementations are not important at this point and should be discussed individually for each case.
The design patterns.
For this project, we will use the SOLID design principles and dependency injection. This will enable us to easily replace small functionalities, allowing us to mix and match different implementations depending on the data source and data target.
by Joche Ojeda | May 5, 2023 | Application Framework, XAF, XPO, XPO Database Replication
I will explain what XAF is just for the sake of the consistency of this article, XAF is a low code application framework for line of business applications that runs on NET framework (windows forms and web forms) and in dotnet (windows forms, Blazor and Web API)
XAF is laser focus on productivity, DevExpress team has created several modules that encapsulate design patterns and common tasks needed on L.O.B apps.
The starting point in XAF is to provide a domain model using an ORMs like XPO or Entity framework and then XAF will create an application for you using the target platform of choice.
It’s a common misunderstanding that you need to use and ORM in order to provide a domain model to XAF
DevExpress team has created ObjectSpace abstraction so XAF can be extended to use different data access technologies ( you can read more about it here https://docs.devexpress.com/eXpressAppFramework/DevExpress.ExpressApp.BaseObjectSpace)
Out of the box XAF provide 3 branches of object spaces as show is the graph below.
XPObjectSpace: this is the object space that allows you to use XPO as a data access technology.
EfCoreObjectSpace: this is the object space that allows you to use Microsoft Entity Framework as a data access technology.
NonPersistenObjectSpace: this object space is interesting as it provides the domain model needed for XAF to generate the views and interact with the data is not attached to an ORM technology so it’s up to us to provide the data, also this type of object space can be used in combination with XPObjectSpace and EfCoreObjectSpace
When querying external data sources, you also need to solve the problem of filtering and sorting data in order to provide a full solution, for that reason DevExpress team provide us with the DynamicCollection class, that is a proxy collection that allows you to filter and sort an original collection without changing it.
Now that we know the parts involved in presenting data in a XAF application, we can define the required flow.
[DefaultClassOptions]
[DefaultProperty(nameof(Article.Title))]
[DevExpress.ExpressApp.ConditionalAppearance.Appearance("", Enabled = false, TargetItems = "*")]
[DevExpress.ExpressApp.DC.DomainComponent]
public class Article : NonPersistentObjectBase {
internal Article() { }
public override void Setup(XafApplication application) {
base.Setup(application);
// Manage various aspects of the application UI and behavior at the module level.
application.SetupComplete += Application_SetupComplete;
}
- Wire the application object space created event.
private void Application_SetupComplete(object sender, EventArgs e) {
Application.ObjectSpaceCreated += Application_ObjectSpaceCreated;
}
private void Application_ObjectSpaceCreated(object sender, ObjectSpaceCreatedEventArgs e) {
var npos = e.ObjectSpace as NonPersistentObjectSpace;
if (npos != null) {
new ArticleAdapter(npos);
new ContactAdapter(npos);
}
}
public ArticleAdapter(NonPersistentObjectSpace npos) {
this.objectSpace = npos;
objectSpace.ObjectsGetting += ObjectSpace_ObjectsGetting;
}
private void ObjectSpace_ObjectsGetting(object sender, ObjectsGettingEventArgs e) {
if(e.ObjectType == typeof(Article)) {
var collection = new DynamicCollection(objectSpace, e.ObjectType, e.Criteria, e.Sorting, e.InTransaction);
collection.FetchObjects += DynamicCollection_FetchObjects;
e.Objects = collection;
}
}
private void DynamicCollection_FetchObjects(object sender, FetchObjectsEventArgs e) {
if(e.ObjectType == typeof(Article)) {
e.Objects = articles;
e.ShapeData = true;
}
}
Full source code here
In conclusion the ObjectSpace abstraction ensures that different data access technologies can be employed, while the DynamicCollection class allows for seamless filtering and sorting of data from external sources. By following the outlined steps, developers can create robust, adaptable, and efficient applications with XAF, ultimately saving time and effort while maximizing application performance.
