Generated for Entity Framework Core migration planning
The DDEC database resides in SQL Server and contains ~40 tables organized into several functional groups. The schema reflects its IMS mainframe heritage with hierarchical parent-child relationships, composite keys, VARCHAR-everything data types, and table-prefixed column names.
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| MGAPROOT | 35 | Calibration master records (root of BOM hierarchy) | MGAPROOT_KEY varchar(10) |
| CMPNTSEG | 17 | Component segments (children of MGAPROOT) | Composite (CMPNTSEG_KEY + CMPNTSEG_PR + FK) |
| CMPATSEG | 9 | Component attributes (children of CMPNTSEG) | Composite (CMPATSEG_KEY + CMPATSEG_PR + FK) |
| CNOTESEG | 3 | Component notes (children of CMPNTSEG) | Composite (CNOTESEG_KEY + CNOTESEG_PR + FK) |
| NOTESEG | 3 | Notes (children of MGAPROOT) | Composite (NOTESEG_KEY + NOTESEG_PR + FK) |
| MGAPCNTL | ~120 | Control parameters for calibration groups | Composite |
| MGAPINAC | ~175 | Inactive parameters | Composite |
| MGAPMISC | ~30 | Miscellaneous calibration parameters | Composite |
| MGAPRULE | ~50 | Rules and constraints for calibrations | Composite |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| SDCRBASE | 3 | Calibration base binary data (child of MGAPROOT) | Composite (KEY + FK) |
| SDBMD3BS | 3 | DDEC3 binary master data (child of MGAPROOT) | Composite (KEY + FK) |
| SDBMD5BS | 3 | DDEC5 binary master data | Composite (KEY + FK) |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| SDD2ROOT | ~10 | DDEC2 sales order root | SDD2ROOT_KEY varchar(10) |
| SDD2UNIT | ~50 | DDEC2 unit data (child of SDD2ROOT) | Composite (UNIT_NBR + FK) |
| SDD2HIST | ~50 | DDEC2 history records (child of SDD2UNIT) | Composite (UNIT_NBR + DATE + TIME) |
| SDD2PROG | ~30 | DDEC2 programming data (child of SDD2UNIT) | Composite (UNIT_NBR + FK) |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| SDD3UNIT | 214 | DDEC3 unit configuration data (standalone root) | SDD3UNIT_UNIT_NBR varchar(10) |
| SDD3HIST | ~100 | DDEC3 history records | Composite (UNIT_NBR + DATE + TIME) |
| SDD3PROG | ~80 | DDEC3 programming records | Composite |
| SDD3FINC | ~20 | DDEC3 finance/billing data | Composite |
| SDD3RECV | ~40 | DDEC3 received configuration data | Composite |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| SDD5UNIT | 245 | DDEC5 unit configuration data (standalone root) | SDD5UNIT_UNIT_NBR varchar(10) |
| SDD5HIST | 221 | DDEC5 history records | Composite (UNIT_NBR + DATE + TIME) |
| SDD5PROG | ~100 | DDEC5 programming records | Composite |
| SDD5FINC | ~20 | DDEC5 finance/billing data | Composite |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| R24NA2US | ~80 | DDEC3 parameter value ranges | Composite (4 fields) |
| R24NA3US | ~20 | DDEC3 input channel definitions | Composite |
| R24NA4US | ~20 | DDEC3 output channel definitions | Composite |
| R24NA7US | 432 | Master series/application config (largest table) | Composite (5 fields) |
| R24NA9US | 12 | Series/application/transmission matrix | Composite (6 fields) |
| R24NB0US | 11 | Transmission codes | RCB01_TRANSMISSION_COD varchar(2) |
| R24NB3US | 17 | Torque curve definitions (by RPM points) | SDD3NB3U_CURVE_ID decimal(4,0) |
| R24NB4US | 8 | Sensor block numbers | RCB41_SENBLKNO decimal(3,0) |
| R24NB5US | 11 | PWM configuration set 1 | Composite |
| R24NB6US | 12 | Input pin configuration | Composite |
| R24NB7US | 12 | Output pin configuration | Composite |
| R24NB8US | 11 | PWM configuration set 2 | Composite |
| R24OCSEG | 3 | OE status codes | Composite |
| R24SPSEG | 10 | Special segment/activity log | Composite |
| Table | Columns | Purpose | Primary Key |
|---|---|---|---|
| DBPACF01 | 5 | User authorization/customer code access | Composite (APPLICATION + USER_ID) |
| CUST01 | 2 | Customer code hierarchy | Composite (CT_CUST_CODE + ST_CUST_CODE) |
| CUST_CODE | 4 | Customer code lookup | UID int identity |
MGAPROOT (Calibration Root)
├── CMPNTSEG (Components)
│ ├── CMPATSEG (Component Attributes)
│ └── CNOTESEG (Component Notes)
├── NOTESEG (Notes)
├── SDCRBASE (Calibration Base Data)
└── SDBMD3BS (DDEC3 Binary Data)
SDD2ROOT (DDEC2 Sales Order)
└── SDD2UNIT (DDEC2 Units)
├── SDD2HIST (DDEC2 History)
└── SDD2PROG (DDEC2 Programming)
SDD3UNIT (DDEC3 Units - standalone)
├── SDD3HIST (DDEC3 History)
├── SDD3PROG (DDEC3 Programming)
├── SDD3FINC (DDEC3 Finance)
└── SDD3RECV (DDEC3 Received Config)
SDD5UNIT (DDEC5 Units - standalone)
├── SDD5HIST (DDEC5 History)
├── SDD5PROG (DDEC5 Programming)
└── SDD5FINC (DDEC5 Finance)
SDD3UNIT/SDD5UNIT
├── R24NA7US (Series/Application Config) - via DDEC3_CODE + APPLICATION + SERIES
├── R24NB3US (Torque Curves) - via CURVE_ID
├── R24NB0US (Transmissions) - via TRANS_TYPE
├── R24NA2US (Parameter Ranges) - via configuration codes
├── R24NB5-8US (I/O Configs) - via CONFIG_ID fields
└── CUST_CODE, DBPACF01 - via CUST_CODE
DBPACF01 (User Authorization)
└── CUST01 (Customer Hierarchy) - via CUST_CODE_LVL1/LVL2
| Column | Data Type | Description |
|---|---|---|
| MGAPROOT_KEY | varchar(10) | PK - Unique calibration identifier |
| MGAPROOT_TYPE | varchar(1) | Calibration type indicator |
| MGAPROOT_NAME | varchar(40) | Calibration name/description |
| MGAPROOT_ROOT_REV_DATE | varchar(5) | Root revision date (YYDDD Julian) |
| MGAPROOT_COMP_REV_DATE | varchar(5) | Component revision date |
| MGAPROOT_GRP_FULL_LOAD_RPM_RATE | varchar(5) | Full load RPM rating |
| MGAPROOT_GRP_CERT_NUM | varchar(4) | Certification number |
| MGAPROOT_GRP_MDL_YR_FRM | decimal(4,0) | Model year from |
| MGAPROOT_GRP_MDL_YR_TO | decimal(4,0) | Model year to |
| MGAPROOT_GRP_DD4_6N4D | varchar(10) | DD4 6N4D group identifier |
| (35 columns total) |
Key column categories:
This is the largest reference table. Pattern for each configurable parameter:
VALUE - The actual valueVALUE_RNG_MIN - Minimum allowed valueVALUE_RNG_MAX - Maximum allowed valueVALUE_FIXED - "Y" means fixed/locked, "N" means configurableCovers: cruise control, VSG droop, PWM configs, analog inputs (30+), digital outputs (8+), sensor configs, protection parameters.
All dates stored as strings in various formats:
Many tables have _REST fields (varchar(1) to varchar(171)) reserved for future expansion. Mainframe legacy pattern.
All columns prefixed with table abbreviation: SDD3UNIT_VSG_INIT_SPEED, MGAPROOT_GRP_MDL_YR_FRM
All foreign keys use WITH NOCHECK. Historical data may have orphaned records. EF Core must handle missing parent scenarios.
Many tables use multi-column primary keys (up to 6 columns for R24NA9US). Requires explicit configuration in EF Core OnModelCreating.
Most transactional tables include:
*_CREATE_DATE/TIME - Creation timestamp*_USER_ID - Creating user*_DWNLD_DATE/TIME - Download timestamp*_CHANGE_CNT, *_DOWNLOAD_CNT, *_REPRG_CNT - Event countersMost PKs are manually assigned varchar fields. No auto-increment except CUST_CODE.UID.
| Database Table | C# Entity | Key Navigation Properties |
|---|---|---|
| MGAPROOT | MgapRoot | Components, Notes, CalibrationBase |
| CMPNTSEG | ComponentSegment | Parent: MgapRoot; Attributes, Notes |
| CMPATSEG | ComponentAttribute | Parent: ComponentSegment |
| SDD2ROOT | Ddec2SalesOrder | Units |
| SDD2UNIT | Ddec2Unit | Parent: SalesOrder; History, Programming |
| SDD3UNIT | Ddec3Unit | History, Programming, Finance, ReceivedConfig |
| SDD5UNIT | Ddec5Unit | History, Programming, Finance |
| R24NA7US | Ddec3SeriesAppConfig | (Reference lookup) |
| R24NB3US | TorqueCurve | (Reference, Units reference via CurveId) |
| DBPACF01 | UserAuthorization | CustomerCodes |
public class Ddec3Unit
{
[Key]
[Column("SDD3UNIT_UNIT_NBR")]
[StringLength(10)]
public string UnitNumber { get; set; }
// Decimal fields map directly
[Column("SDD3UNIT_VSG_INIT_SPEED")]
public int? VsgInitialSpeed { get; set; }
// String dates with typed accessor
[Column("SDD3UNIT_EST_BLD_DATE")]
[StringLength(8)]
private string _estimatedBuildDateString;
[NotMapped]
public DateTime? EstimatedBuildDate
{
get => DateHelper.ParseYYYYMMDD(_estimatedBuildDateString);
set => _estimatedBuildDateString = DateHelper.FormatYYYYMMDD(value);
}
// FK to reference table
[Column("SDD3UNIT_CURVE_ID")]
[ForeignKey(nameof(TorqueCurve))]
public int? CurveId { get; set; }
// Navigation properties
public virtual TorqueCurve TorqueCurve { get; set; }
public virtual ICollection<Ddec3History> History { get; set; }
public virtual ICollection<Ddec3Programming> ProgrammingRecords { get; set; }
}
For tables with 200+ columns (SDD3UNIT, SDD5UNIT, R24NA7US), use partial classes:
// Ddec5Unit.cs - Core identity fields
// Ddec5Unit.Sensors.cs - Sensor configurations
// Ddec5Unit.DigitalIO.cs - Digital I/O mappings
// Ddec5Unit.Protection.cs - Protection parameters
public static class DateHelper
{
public static DateTime? ParseYYYYMMDD(string s) { ... }
public static DateTime? ParseYYDDD(string julian) { ... } // 5-char Julian
public static DateTime? ParseCYYMMDD(string s) { ... } // 7-char century format
public static string FormatYYYYMMDD(DateTime? d) { ... }
}
| Phase | Tables | Risk |
|---|---|---|
| 1 - Reference (simple) | R24NB0US, R24NB3US, R24NB4US, CUST_CODE, CUST01, DBPACF01 | Low |
| 2 - Calibration master | MGAPROOT + children (CMPNTSEG, CMPATSEG, CNOTESEG, NOTESEG) | Medium |
| 3 - Complex reference | R24NA2US, R24NA7US (432 cols!), R24NA9US, R24NB5-8US | Medium-High |
| 4 - DDEC2 family | SDD2ROOT -> SDD2UNIT -> SDD2HIST, SDD2PROG | Medium |
| 5 - DDEC3/5 families | SDD3UNIT (214 cols) + children, SDD5UNIT (245 cols) + children | High |
| 6 - Supporting | R24OCSEG, R24SPSEG, binary tables (SDCRBASE, SDBMD3/5BS) | Low |
| File | Location |
|---|---|
| Complete DDL | database/schemas/DDEC Table Schema.sql |
| Create statements | src/ddec-web/src/main/resources/sql/CREATE_DDL.sql |
| Foreign keys | src/ddec-web/src/main/resources/sql/ALTER_DDL.sql |