Working with semi-structured data

[Anonymous]

Hi!

Currently i'm working on a project which requires me to work with semi-structured data in dataverse and a model driven app. This means that i have a table with say for example "vehicles", and in this table you can have bikes, cars, unicycles etc. Depending on this type, you'll have different vehicle properties. For example, a car will have properties that are related to engine/power values, while a bike may have a boolean whether or not it contains a luggage carrier. 

I'm struggling with finding a best practice method to handle this:

1. Create a different table, fields, forms and views per vehicle type, basically splitting up the vehicles table to cater to every type. basically have the user choose the type for every vehicle first before doing anything
2. Create one big table with all the possible properties and have the form dynamically show hide the needed properties depending on vehicle type.
3. Create some properties table with a relation to the vehicles table that will contain vehicle properties as rows.

Each of these have pro's and cons;

1. Cumbersome for the user to work with and when doing integrations you'll have to be constantly splitting and merging tables. Also if a new vehicle type is introduced, new tables/views/forms will need to be created.
2. Easier for the user since all in one place, but can grow pretty heavy on performance and maintenance if you have a lot of properties and vehicle types.
3. Most logical from a normalization standpoint, however you wont be able to really guide the user as you wont be able to make use of specific field types depending on the property, so every property value would become string based thing, while in some cases an option set would be needed. 
4. I know that Dynamics Sales has a product catalog data model but the client does not have Dynamics Sales to make use of this.

Am i missing some option here, or is there a specific best practice approach to take here? 

Thanks!

[dpoggemann · Accepted solution]

Hi @Anonymous ,

You are struggling with something that I have struggled with in many project in the past.  There is no Object Oriented nature with polymorphism / inheritance etc. in this environment so you have to work around this.  The only case where this does kind of exist is with Activity entity and then when you create new Activity types they basically inherit from the base. 

Microsoft did just add some polymorphic lookup capability (in preview):  https://powerapps.microsoft.com/en-us/blog/announcement-multi-table-lookups-are-now-available-as-a-preview/ 

Overall in the past I have tried multiple of these options and really the architect in me wanted to create the base (super class) table with some of the core fields and then sub-classed tables with specific fields for the different entities...  I have honestly struggled with this in the Dataverse over the years because the design is sound but the system really doesn't support it and makes it more complex in many ways.

What I have done and has worked very well in most cases (recently did with large project) is create single table with all the fields and utilized business rules to hide / show, require / optional, etc. based on the "type" selected on the table.  Overall this has provided great flexibility, easy to build, simpler and dynamic for users, simpler to maintain, and easier for users.

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[cchannon]

Qualifier up front: generally, I don't recommend you follow this approach because it is code intensive, but I wanted to put out here an option for achieving polymorphism in Dataverse:

You can use a Virtual Entity to achieve a kind of polymorphism, if you don't mind thinking about it in the opposite structure from classic examples. So, where in OOP you would have classes Mammal and Amphibian which inherit from class Animal in order to give both Monkey and Salamander the base attribute of heartrate, in Dataverse you can use Virtual Entities to build that abstraction in reverse; an Animal Virtual Entity that queries Mammal and Amphibian and surfaces the attribute heartrate. Effectively, with a Virtual Entity on a Custom Data Provider, you can point inward at multiple Dataverse tables to create your own polymorphic constructs like a custom ActivityPointer.

Upsides: you can build complex interrelated constructs at more than just a lookup level

Downsides: You have to create and manage all the schema on all the tables; there is no inheritance of schema, so your code becomes a bit brittle and requires updates for every minor evolution of the schema.

[Anonymous]

Even though this gets labour intensive pretty quickly with a lot of fields, i guess in most use cases it will suffice. Thanks!

It would be great if we could have support for Semi-structured data in dataverse somehow. Or at least some standard canvas component we could load into a model-driven app. In this scenario we could put the basic data of the object in dataverse, and the properties of the object in a CosmosDB with the key as the object key. The Canvas component would read the nested JSON as automatically key value pairs and generates text fields for each value. Something like this, although it wouldn't solve UI/UX issues due to the lack of field type support.