Visits: 23
Shifting Towards Simplicity: Using Single Unique Identifiers in Our Data Model
Nobuyuki SAMBUICHI
ISO/TC295 Audit data services/SG1 Semantic model Convener
In our current table and field definitions, we have several tables that define their primary key as a combination of multiple fields. I recommend that we use a single primary key to define our object classes from our table definition. Primary keys and referenced identifiers (foreign keys) should be defined more simply and clearly in UML associations.
Provide some pros and cons of defining a primary key on multiple fields.
Provide the advantage of designing the semantic model of object classes using a single unique identifier (primary key) attribute.
Convince non-technical experts to change the table definition to an object class definition with a single unique identifier.
Sure, let’s break down your questions one by one.
Pros and Cons of defining a Primary Key on Multiple Fields (Composite Key):
Pros:
-
Natural Data Relationships:
Composite keys can naturally represent data relationships, such as a student taking multiple courses. A composite key of student ID and course ID can uniquely represent each course taken by each student. -
Data Integrity:
Composite keys can help enforce data integrity at a granular level, as the combination of fields must be unique.
Cons:
-
Complexity:
Composite keys can make queries more complex. You have to specify all parts of the key in the WHERE clause of a query, making SQL harder to write and read.
Join Operations: Using composite keys can make join operations more complex, as you need to join on all parts of the key. -
ORM Complications:
Object-Relational Mapping (ORM) frameworks often have difficulties working with composite keys, as they are harder to map to objects than single-value keys.
Advantages of Designing Semantic Model of Object Classes Using a Single Unique Identifier (Primary Key) Attribute:
-
Simplicity:
A single unique identifier makes it easier to reference a specific instance of an object, simplifying SQL queries, updates, and deletes. -
ORM Compatibility:
ORM frameworks generally work best with single-value keys. A single unique identifier aligns well with object-oriented programming principles, where each object typically has a single unique identifier. -
Scalability:
Single-value keys can make it easier to distribute data across multiple nodes in a distributed database, as each object can be easily hashed to a specific node.
Guiding Non-Technical Colleagues Towards the Adoption of a Single Unique Identifier in Object Class Definition
Now, in order to guide non-technical colleagues towards the adoption of an object class definition with a single unique identifier, you might consider highlighting the following points:
-
Simplification of Data Access:
A single unique identifier allows for easier data access, reducing complexity in both coding and understanding how data is connected. It’s akin to having a unique ID for every book in a library, making it easier to find and catalog each item. -
Performance:
Databases perform better when searching, sorting, or joining on single-value keys, making our applications faster and more responsive. -
Future Proofing:
Modern coding practices and ORM tools are designed to work optimally with single-value keys. By adopting this practice, we future-proof our system, making it easier to update or switch to new tools in the future. -
Data Consistency:
Using a single unique identifier can help prevent duplicate records and improve the consistency of our data, which helps ensure the information we’re working with is accurate and reliable.
Remember, it’s all about making data easier to work with and understand, both for us and for the computer systems we’re using.
Example: Base module Accounting Period
Provides an example scenario where changing to a single unique identifier is better than combining multiple fields. And provide reasons why this change is important with sample use case.
For accounting data, the entry should report period for this record.
Current period table is defines a primary key with combination of Fiacal Year and Accounting Period.
Folllowing is the current definition. Provide your updated object class with single unique identifier.
|
No. |
Name |
Datatype |
Description |
|
1 |
Fiscal Year |
String |
The fiscal year in which the calendar date occurs. The year shall be shown in four digits as “YYYY”, which is part of the extended format and the “YYYYMM-DD” in ISO 8601-1. |
|
2 |
Accounting Period |
String |
The accounting period in which the calendar date occurs. EXAMPLE W1–W53 for weekly periods, M1–M12 for monthly periods, Q1–Q4 for quarterly periods, and from any beginning date to any ending date. |
|
3 |
Accounting Period Beginning Date |
Date |
The calendar beginning date of the current accounting period. |
|
4 |
Accounting Period Ending Date |
Date |
The calendar ending date of the current accounting period. |
Absolutely, let’s start by understanding the scenario. It appears we’re currently using a combination of “Fiscal Year” and “Accounting Period” as a composite primary key. While it does work in most scenarios, it might create unnecessary complications in the system.
An example of a problem that can arise due to this composite key setup is when the data needs to be referenced elsewhere. For example, if we need to link an accounting transaction to a specific accounting period, we would have to include both the “Fiscal Year” and “Accounting Period” in the transaction record. This can become cumbersome and increase the risk of data inconsistencies.
To solve this, we can create a single, unique identifier for each accounting period. This unique identifier, or primary key, can then be used to link to the accounting period from other tables.
Here is a modified version of the object class for your consideration:
|
No. |
Name |
Datatype |
Description |
|
1 |
Period ID |
String |
A unique identifier for the accounting period, which combines fiscal year and accounting period in a single attribute. Format: “YYYYMM” for monthly periods, “YYYYQX” for quarterly periods, “YYYYWX” for weekly periods. |
|
2 |
Fiscal Year |
String |
The fiscal year in which the calendar date occurs. The year shall be shown in four digits as “YYYY”, which is part of the extended format and the “YYYYMM-DD” in ISO 8601-1. |
|
3 |
Accounting Period |
String |
The accounting period in which the calendar date occurs. EXAMPLE W1–W53 for weekly periods, M1–M12 for monthly periods, Q1–Q4 for quarterly periods, and from any beginning date to any ending date. |
|
4 |
Accounting Period Beginning Date |
Date |
The calendar beginning date of the current accounting period. |
|
5 |
Accounting Period Ending Date |
Date |
The calendar ending date of the current accounting period. |
The benefit of making this change is evident when we consider the scenario of reporting an accounting transaction. Instead of having to report two different pieces of information (Fiscal Year and Accounting Period), we now only need to report the “Period_ID”. This makes the data easier to work with, improves data integrity, and simplifies our database and object model.
