Hi All
I have been made aware of a pretty fatal implementation problem with my project. I had a table that was gathering results for a specific business process (lets call it a survey for simplicity). That table ended up holding close to 250 custom entities. I was told by someone in the game recently that this was going to cause me serious problems long-term with app crashes and extremely slow lookups. He has recommended taking the time now to normalise the tables. I dont like the idea of re-inventing the wheel once things are in production so I am working on fixing this now but struggling with implementation.
I have worked on this and now have the 250 custom entities split up into 7 different tables. The problem I now have is that I am struggling to correctly implement relationships and the model-driven app is now a totally different user experience. To elaborate a little:
I dont know exactly what the relationship implementation for this should look like because its, from my interpretation, supposed to be a 1:1 relationship and they dont exist in Dataverse. I have a single parent record, and then all 6 other tables are child records that simply hold the various entities for that parent record. Those relationships are also configured as parental relationships so that if the parent record is deleted, so are the child records in the 6 other tables. There should realistically be no more than 1 child record in each table for each parent record. While thats not exactly the end of the world if there is, it just adds extra steps to pulling the records and training/making end users aware that only the 'newest' record will be utilised. There is no value or function in having a 1:N relationship here that I can identify, but my fundamental database knowledge is quite intermediate.
The model-driven app functionality sadly changes significantly now that there is multiple tables to contend with. When it was a single table, I could have a row of tabs along the top to swap between different content areas. For the business process, this was really convenient because the data entry isn't necessarily 'linear'. Being able to half-fill some entities and come back to that tab later had a lot of value. Now that I have child tables, the only way I am AWARE (please someone correct me...) that I can bring in that data is to add sub-grids for those tables. The problem with that method is that when you click + to create a new record in that sub-grid, you need to fill the whole thing out and save it to create the record. You cant leave it half-way and come back. Obviously this is something that can be fixed by refining the business procedures and workflows from a mechanical perspective, but it would be really nice if I didn't have to. The only way I am aware of that I can do this now is by changing to a Canvas app, collecting the data from the user, and then using Patch to populate each table. Its a lot more work than the original, single table, model-driven approach.
Hoping this all makes sense and someone can give me some guidance with this!
Regards,
Sheik.
