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.
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
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.
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);
}
}
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.
If you are running Xaf Blazor in ubuntu 18.04 you might have seen the following exception
The type initializer for ‘Gdip’ threw an exception.
at DevExpress.ExpressApp.Actions.ActionBase.OnHandleException(Exception e) at DevExpress.ExpressApp.Actions.ActionBase.ExecuteCore(Delegate handler, ActionBaseEventArgs eventArgs) at DevExpress.ExpressApp.Actions.PopupWindowShowAction.DoExecute(Window window) at DevExpress.ExpressApp.Actions.PopupWindowShowAction.DialogController_Accepting(Object sender, DialogControllerAcceptingEventArgs e) at DevExpress.ExpressApp.SystemModule.DialogController.Accept(SimpleActionExecuteEventArgs args) at DevExpress.ExpressApp.SystemModule.DialogController.acceptAction_OnExecute(Object sender, SimpleActionExecuteEventArgs e) at DevExpress.ExpressApp.Actions.SimpleAction.RaiseExecute(ActionBaseEventArgs eventArgs) at DevExpress.ExpressApp.Actions.ActionBase.ExecuteCore(Delegate handler, ActionBaseEventArgs eventArgs)
The error is caused by missing dependency, so the DotNet runtime itself will throw that exception. Also, I want to highlight that the exception is not related to XAF, you can read more about this problem here https://github.com/dotnet/runtime/issues/27200
To get the missing dependency just open a console and run the following commands
I have been using XPO from DevExpress since day one. For me is the best O.R.M in the dot net world, so when I got the news that XPO was going to be free of charge I was really happy because that means I can use it in every project without adding cost for my customers.
Nowadays all my customer needs some type of mobile development, so I have decided to master the combination of XPO and Xamarin
Now there is a problem when using XPO and Xamarin and that is the network topology, database connections are no designed for WAN networks.
Let’s take MS SQL server as an example, here are the supported communication protocols
TCP/IP.
Named Pipes
To quote what Microsoft web site said about using the protocols above in a WAN network
In a fast-local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable with regard to performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the interprocess communication (IPC) mechanisms communicate between peers.”
So, what other options do we have? Well if you are using the full DotNet framework you can use WCF.
So, it looks like WCF is the solution here since is mature and robust communication framework but there is a problem, the implementation of WCF for mono touch (Xamarin iOS) and mono droid (Xamarin Android)
You can read about Xamarin limitations in the following links
I don’t want to go into details about how the limitation of each platform affects XPO and WCF but basically the main limitation is the ability to use reflection and emit new code which is needed to generate the WCF client, also in WCF there are problems in the serialization behaviors.
So basically, what we need to do is to replace the WCF layer with some other technology to communicate to the database server
The technology I’ve selected for this AspNetCore which I would say is a really nice technology that is modern, multi-platform and easy to use. Here below you can see what is the architecture of the solution
AspNetCore
Rest API
So, what we need basically is to be able to communicate the data layer with the data store through a network architecture.
The network architecture that I have chosen is a rest API which is one of the strong fronts of AspNetCore. The rest API will work as the server that forward the communication from XPO to the Database and vice versa, you can find a project template of the server implementation here https://www.jocheojeda.com/download/560/ this implementation references one nuget where I have written the communication code, you can fine the nuget here https://nuget.bitframeworks.com/feeds/main/BIT.Xpo.AgnosticDataStore.Server/19.1.5.1
Sometimes we need to have clientside events and handle them on the server side code behind, that in a simple asp.net web page is really easy, you just have to execute a javascript that executes an HTTP request to the server. Now the question is, how do we do that in XAF?
Well, the concept is basically the same but you need to know XAF architecture the problem is that most of the code needed is not documented, but after a while, I manage to figure it out, so let’s get started.
1) Create a XAF web application
2) On your web module add a view controller
3) Implement the interface IXafCallbackHandler on the controller you just added in step 2, this is the method that will be called as a callback from javascript. This interface is not documented on the DevExpress website
4) In your view controller add a property to access XafCallbackManager
5) Override the OnViewControlsCreated method and register your callback, in this example, the name of the callback is “MyScript”
6) Now add a simple action and wire the execute event, on the execute event cast the frame as a web window and register a startup script. The code surrounded with the blue line is the javascript that triggers the callback in the callback manager, the code surrounded with red is the id if the script that we are listening for, it should match the name of the script registered on the handler in the previous step.
To execute the callback somewhere in your javascript you have to execute the following function RaiseXafCallback, this function is not documented on the DevExpress website
7) Run your application and execute the simple action added in step 6, when the javascript finish executing, the method you implemented on step 3 will be executed.
The code for this article is here the full example is in GitHub
