Best way to design a data model that has common base table and multiple permutations

[camer314]

A straight forward question for the experts here who know more than me.

We have a requirement for a table that will be 90% common across all businesses (possibly 100). Each business adds their own specific columns and then for each business there will be specific child tables, some possibly the same but mostly completely separate.

What is the best design:

• Have a single base table with columns for all lines of business. Create forms and views for each business and create N:1 relationships for each child tables back to base. Forms for each business include grids for child tables.
• Or entirely split base table, one for each business, so the hierarchy from top to bottom is separate

I suspect the answer is the first, but then wont the base table get quite bloated and wide? But then on the second its a management nightmare.

Surely I am not the first with this issue, so what does the group wisdom have to say?

[gulshankhurana]

Hi @camer314 

There are more than one ways to achieve this as you said and every option has its ups and downs. Similar question was asked a bit earlier so, I'm being a bit lazy and, instead of typing my answer, I have pasted the link to that post with my answer (and alternative options suggested by others):

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-to-extend-standard-tables-for-specific-l...

I hope you find the information useful. Please give it a thumbs up if you do and please accept it as a solution if it helps resolve your query as this will also help others with similar questions.

Kind regards

Gulshan

[MarioRing · Accepted solution]

Hi,
You're right - this is a frequent problem that we face in many of our projects. The truth is there is no universal solution for that kind of challenge, so you must wisely consider all the pros and cons.

Think about these questions:

• What is the number of variants of the forms/processes/views? 100 is a significant number.
• What do all these variants have in common? Divide and conquer - is it possible to reduce the number of variants by groping them into logical sets?
• Look at this problem in terms of reporting. Which option is more convenient from this perspective?
• What is the roadmap for future changes in the process and the data structure? What kind of modifications do you expect?
• What if all departments see all the fields on the forms, even if they are not relevant to them?
• Why does your business need to have multiple variants of the application? Is it only cosmetic reasons or the processes and business logic are different?
• Do you expect to add another variant to the system in the future (for instance - a new department in your organization?)
• What would be the cost of rebuilding the application from option 1 (single common table) to option 2 (multiple tables)? How would it look like otherwise, from 2 to 1?
• Assume several change requests during the year and estimate how would be the cost of change for both options.
• How difficult for your organization would be managing business rules and Java Scripts (for instance, if you don't have JS developer on board)?

Let's meet in the middle - can you transpose your structure into core/common elements and department-specific ones? Create one core table that covers common interests and add another related tables that contain additional fields related to given variants. Remember you can embed the editable form of one table in another one.

Think about creating flexible form, where you can manage the settings for different LOB using your application rather than customizing all the one hundred variants. I mean creating a new entity "Line of Business", which gathers all the settings related to form for given LOB.

For instance, you create a Yes/No field called "Show Project Number". Then you write a JavaScript for the main form, which queries for the LOB settings based on the record data or current user's department, and applies selected business rules. In this case it will show/hide the chosen field.

You can create one hundred records of LOBs and bulk edit them easily, to change the business rules.

Personally, according to my experience, I opt for reusing as many elements as you can, instead of creating slightly different objects. Don't multiply instances of similar content - the best code is the one that has never been written. Probably the best option is to use the same data structure for all variants, but - if that would be ineffective in the future, you can always exclude part of your application if necessary and move it away to manage it separately.

Please note, we don't know all the details about your project, and the best solution for your case may be the opposite;).

[camer314]

Thanks for the comprehensive reply. Aside from all the detail, I think I was mostly after confirmation that there is not some huge piece of existing functionality that I somehow missed 🙂