Need to build a list of items that have their own datasets attributed - I hope that made sense!

[rknight]

Hello!

I apologize in advance if this is hard to follow. I am building an application for work where we build scopes for projects. I want to create a new scope that has multiple tabs (General, Scope, Software, Network, Servers, SAN, Backup, Workstation, Cabling, and Labor) and saves the data entered in fields on each of those tabs under the title of the overarching scope. Similar to the photo below (1 is the main scope; 2 and 3 are, for example, General and Software; 4 and 5 are data points within General that are still to be connected to the main scope). 

I understand I need some kind of database, but I'm a little at a loss when it comes to how to hold that data (SQL, SharePoint, etc.). Any help is greatly appreciated, and please feel free to ask questions because I know this is may not be the clearest explanation.

Thank you!

@RandyHayes You're a genius, so I figured I'd tag you in this!

[GarethPrisk]

Is Dataverse an option for your database? Not sure what your licensing situation is.

As far as modeling the data, it probably depends on how flexible the data is expected to be.

• Your image above implies that there are 4 tiers - is this always true? Could it branch deeper, or even shorter, in some scenarios?
• Are the tiers different in any way? Would you define something at tier 2 differently than say tier 1 or 3?

A simple strategy, assuming these are fixed tiers, would be to define separate tables (in whatever database you want).

• Tier 1
• Tier 2
  • Related to Tier 1
• Tier 3
  • Related to Tier 2
  • Optionally related to Tier 1
• Tier 4
  • Related to Tier 3
  • Optionally related to Tier 2 and 1

This is a bit archaic, but accomplishes the result. This would allow you to view the data at any tier and be able to correlate back to other locations within the tiers. It would be fixed, but you could create a scenario of a branch which was only Tier 1, 2, and 3.

As noted above, you can optionally relate a Tier 3 all the way to the Tier 1 level, if it helps with reporting/viewing data. The tricky part is just ensuring you are entering the correct data (when creating a Tier 4, it is linked to a Tier 3 and it's related Tier 2, and then the Tier 2's Tier 1. This is perfectly possible, but the hierarchy has the potential to become misaligned unless you are reconciling data. Meaning, if you suddenly moved a Tier 2 under a new Tier 1, then the child Tier 3's would also need to be updated to align under the correct Tier 1. This is avoided if you simply have single steps.

If you got the Dataverse route, you could create a single table with a self-referential relationship. This would allow you to enter any tree of records you wanted. This becomes tricky for visualizing/reporting, but is a valid approach for dynamic trees/hierarchies.

[rknight]

I'm looking into Dataverse. It looks like it may be the solution I'm looking for, but I need to dig deeper into it's capabilities. 

To clarify a bit more with an example, say I want to create a scope for ABC Company. ABC Company, when selected, will bring up data in different tabs: General, Scope, Network, a total of 10 subsets of data. When the data is entered, I can go back to a home list of all created scopes, select a different scope, XYZ Corp., and that scope's data appears in those tabs. 

Based on that, do you still think Dataverse would be the best option? If not, is there a way to do it just in SharePoint/Excel tables?