❔ WYSIWYG model storage - hierarchy table(s) vs JSON column
Using EF + SQL Server, I need to support saving a
Template which consists of Pages and Elements maintained via a WYSIWYG editor. Element is an abstract class with derived classes like Section (which corresponds to a div), Text, Image, and Chart, each with their own unique properties. A Section can contain another Section, for example, so the data model needs to be hierarchical.
Initially, I considered using a hierarchy table where an Element had an optional parent - public abstract class Element { public int? ParentElementId { get; set; }. It's easy enough to query and hydrate into a read model suitable for consumption, but my assumption is that it would be rather complex to persist changes for the whole disconnected graph since users can add/remove pages, add/remove elements, move an element from one section to another (even across pages), update all style properties of a given element, etc.
I was considering using a JSON column for the whole hierarchy instead since it's very unlikely we'll need to query any of that info (and we can use SQL Server JSON support if absolutely necessary) and it would be incredibly easy to save the whole hierarchy, just re-assign a single variable to the hierarchy constructed from the WYSIWYG editor.
I've considered the performance/storage implications of JSON, and the discipline required to maintain a backward compatible model (since we'd be giving up migrations in favor of JSON). I'm fine with both of those tradeoffs. Is there anything I'm overlooking? Anything about working with that disconnected graph with EF Core that could make a relational model easier? Has anyone solved this problem before with JSON columns?2 Replies
yeah, this very much sounds like you should treat it as flat data
JSON, XML, whatever format is appropriate to you
you will never need to query upon subsections of these templates, yes? Then you get no benefit from maintaining them as separate elements with querying relationships
if you're concerned about versioning, you can consider more robust formats than JSON
something like maybe protobuf, which has versioning control baked in
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.