Hello Community,
Background:
For some reason Acumatica database design team instead of adding DailyFieldReportId field to PMTimeActivity and EPEquipmentDetail has decided to add two dedicated tables that only holds references to the Key fields of these two tables and DailyFieldReport.
1) The DailyFieldReportEquipment has only 4 fields as follows:
- DailyFieldReportEquipmentId which is an auto incremental Identity field and doesn’t really add any values.
- DailyFieldReportId which holds a reference to [DailyFieldReport.DailyFieldReportId]
- EquipmentTimeCardCd which holds a reference to [EPEquipmentDetail.TimeCardCD] and
- EquipmentDetailLineNumber which holds a reference to [EPEquipmentDetail.LineNbr]
The three reference fields are also set to be the Key Fields of the DAC.
2) The DailyFieldReportEmployeeActivity has only 3 fields as follows:
- DailyFieldReportEmployeeActivityId which is an auto incremental Identity field like the DailyFieldReportEquipmentId and doesn’t really add any value.
- DailyFieldReportId which holds a reference to [DailyFieldReport.DailyFieldReportId]
- EmployeeActivityId which holds a reference to [PMTimeActivity.NoteID]
The two reference fields are also set to be the Key Fields of the DAC.
Reasoning to drop these tables
After quite a bit of brainstorming to comprehend why Acumatica has not added the DailyFieldReportId field to PMTimeActivity and EPEquipmentDetail and instead has created two separate tables, I couldn’t find any good reason. The current design:
- Doesn’t offer a better performance
- Doesn’t simplify the business logic and/or coding
- Doesn’t simplify the reporting and data extraction
- It is very confusing and without knowledge of reading the code or asking Acumatica Support it is not possible for a power user to figure out the table joins to extract data let say in a GI. For example, if some of your employee enter their time using Employee Timecard and some using Daily Field Report and you need both types of times in an GI including DFR ID you have to join lots of useless table to get the desired results.
- Doesn’t offer saving on database space usage
- Considering the auto-numbering nature of [EPEquipmentDetail.LineNbr] we have noticed so mismatch between EPEquipmentDetail and DailyFieldReportEquipment
- Gives us a hard time on a decent customization work we have done on DFR specially with DailyFieldReportEquipment
Request:
Please drop DailyFieldReportEquipment and DailyFieldReportEmployeeActivity tables and instead add the DailyFieldReportId reference to the PMTimeActivity and EPEquipmentDetail
Summary of Communication with Acumatica and End Result:
I have been going back and forth with Acumatica in different levels asking please either provide an explanation about the benefits of this design (we believe it is absolutely a wrong design) or drop these two tables that only keep references to the unique values without any additional data.
Oddly the response I have received after four months is that neither we provide any explanation nor we drop these useless and redundant tables.
Can anyone has come across this issue or does anyone have any idea about these tables design and its benefits?