1. Introduction
This document outlines the rationale, implications, and recommended practices for the restructuring of the activities table within the Safran Project database. In response to increasing demands for user field expansion, the existing monolithic structure has been redesigned to improve scalability, maintainability, and performance. The changes affect both the database schema and related application logic, and this guide is intended to support developers, database administrators, and stakeholders in understanding and adapting to the new structure.
2. Rationale Behind the Change
Safran has seen a growing demand for expanding the number of user-defined fields in its solution. These fields—used to store references, text, dates, flags, decimals, durations, and outline codes—were previously embedded directly within the activities table. However, as the number of supported fields increases, maintaining them within a single table has become unsustainable.
To address this, the activities table is being restructured. User field data will now be stored in dedicated tables, each tailored to a specific data type. This modular approach improves scalability, simplifies maintenance, and enhances performance by reducing the column count in the core activities table.
3. Implications of the Change
The activities details are stored in tables “activities” and “activity_texts” among others. The text details have already been split several versions ago, and now the reminding user fields will also be stored in individual database tables.
3.1. Database Structure
The following new tables will host user field data:
• activities
• activity_texts
• activity_date
• activity_decimal
• activity_flag
• activity_duration
• activity_outline
• activity_reference
Each of these tables includes:
• seq and net_id: Foreign keys linking to the activities table (with cascade delete).
• field_nr: Identifies the user field number (1–100).
• field_value: Stores the actual value, typed according to the table's purpose.
3.2. Code and Procedure Updates
- Triggers and stored procedures referencing the old structure must be updated.
- Source code interacting with the database must be revised to reflect the new table layout.
- Rewrite of the SQL commands need to be adapted to the new table layout.
- SQL commands as queries SQL queries could be adapted to join or pivot data from the new tables.
- Alternative you rewrite the SQL to best match your need.
4. Accessing Safran Project Data
When developing external applications that need to access Safran Project data, Safran recommends using the Safran Integration API for a standardized and supported interface. However, for solutions embedded within Safran Project—such as localized implementations or reports developed in PowerBuilder—direct SQL access may be necessary and beneficial.
In such cases, developers should be aware that the SQL queries related to activity user fields will require adjustments due to the new table structure introduced in this update.
5. Optional Practices for Adapting
5.1. Update SQL Queries
Use JOIN and PIVOT to retrieve user field data. For example:
- To retrieve date fields:
SELECT a.seq, a.net_id, d1, d2, d3 FROM activity_date ...
To combine date and reference fields:
- To combine date and reference fields:
- Use UNION ALL and PIVOT with string casting (TO_CHAR) to unify data types.
- Alternative each SQL command such as query could be performed in separate commands.
5.2. Ensure Referential Integrity
Rely on the cascade delete behaviour to maintain clean relationships between activities and user field tables.
5.3. Modularise Application Logic
Refactor application code to treat user fields as modular components, allowing for easier future expansion.
5.4. Document Field Usage
Maintain clear documentation of which field_nr corresponds to which business logic or UI element.
6. SQL Samples
Here are some examples of SQL SELECT commands which could be used to mimic previous data structure.
6.1. Date Fields
Previously developers would use SQL commands as this:
SELECT a.an, a.seq, a.net_id, d1, d2, d3, d4, d5, d6, d7, d8, d9, d10
FROM activities a
WHERE a.net_id = 1;
Such could be converted to the following SQL for a similar result.
SELECT
pivot_table.an, pivot_table.seq, pivot_table.net_id,
[D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10] -- add more as needed
FROM (
SELECT
a.an, a.seq, a.net_id,
'D' + CAST(ad.field_nr AS nvarchar) AS field_name,
ad.field_value
FROM activities a
JOIN activity_date ad ON a.seq = ad.seq AND a.net_id = ad.net_id
WHERE a.net_id = 1
) AS source
PIVOT (
MAX(field_value)
FOR field_name IN ([D1], [D2], [D3], [D4], [D5], [D6], [D7], [D8], [D9], [D10]) -- extend as needed
) AS pivot_table;
6.2. Date and Reference Fields combined
Previously developers would use SQL commands as this:
SELECT a.an, a.seq, a.net_id, d1, d2, d3, d4, d5, d6, r1, r2, r3, r4, r5, r6
FROM activities a
WHERE a.net_id = 1;
Such could be converted to the following SQL for a similar result.
6.2.1. Microsoft SQL
SELECT pivot_table.seq, pivot_table.net_id,
-- Pivoted activity_date fields
[D1], [D2], [D3], [D4], [D5], [D6],
-- Pivoted activity_reference fields
[R1], [R2], [R3], [R4], [R5], [R6]
FROM (
-- Combine both sources and cast values to nvarchar
SELECT a.seq, a.net_id,
'D' + CAST(ad.field_nr AS nvarchar) AS field_name,
CONVERT(nvarchar, ad.field_value ,120) AS field_value
FROM activities a
JOIN activity_date ad ON a.seq = ad.seq AND a.net_id = ad.net_id
WHERE a.net_id = 1
UNION ALL
SELECT a.seq, a.net_id,
'R' + CAST(ar.field_nr AS nvarchar) AS field_name,
CAST(ar.field_value AS nvarchar) AS field_value
FROM activities a
JOIN activity_reference ar ON a.seq = ar.seq AND a.net_id = ar.net_id
WHERE a.net_id = 1
) AS source
PIVOT (
MAX(field_value)
FOR field_name IN (
[D1], [D2], [D3], [D4], [D5], [D6],
[R1], [R2], [R3], [R4], [R5], [R6]
)
) AS pivot_table;
6.2.2. Oracle
SELECT seq, net_id, D1, D2, D3, D4, D5, R1, R2, R3, R4, R5
FROM (
-- Combine both sources and cast values to string
SELECT a.seq, a.net_id,
'D' || ad.field_nr AS field_name,
TO_CHAR(ad.field_value) AS field_value
FROM activities a
JOIN activity_date ad ON a.seq = ad.seq AND a.net_id = ad.net_id
WHERE a.net_id = 1
UNION ALL
SELECT a.seq, a.net_id,
'R' || ar.field_nr AS field_name,
TO_CHAR(ar.field_value) AS field_value
FROM activities a
JOIN activity_reference ar ON a.seq = ar.seq AND a.net_id = ar.net_id
WHERE a.net_id = 1
)
PIVOT (
MAX(field_value)
FOR field_name IN (
'D1' AS D1, 'D2' AS D2, 'D3' AS D3, 'D4' AS D4, 'D5' AS D5,
'R1' AS R1, 'R2' AS R2, 'R3' AS R3, 'R4' AS R4, 'R5' AS R5
)
);
7. Summary
To support the growing need for user-defined fields, the activities table in the Safran database has been restructured. User field data is now stored in dedicated tables based on data type, improving scalability and maintainability. This change affects SQL queries, triggers, procedures, and application logic.
The document provides SQL examples to help replicate the previous structure using joins and pivoting techniques. For external systems, we recommend using the Safran Integration API to access user field data in a consistent and future-proof manner.