Best Practices for Attribute Data Modeling in UN

[VishApte]

This is more of a discussion than a question...

With UN model, we have only 3 feature classes per domain; Junction, Device and Line to model the assets. Most of the "assets" will be either a Device or a Line. I understand that this is by-design for performance reasons. In traditional data modeling, we used separate feature classes for asset types e.g. Fuse, Switch, Circuit Break, Transformer, Service Connection, HV Conductor, LV Conductor, Busbar etc. for Electric Distribution model. 

Working with only two meaningful feature classes for some disparate asset types creates an under-normalised data model. It either creates a huge table with only a fraction of fields populated for any row or overloads fields for each subtype e.g. "Rating" attribute will be used as "KVA Rating" for a Transformer, "Load Break amps" for Fuse, "Maximum current" for a Regulator and so on. Some of the clients I work with tend to store electrical characteristics for devices and lines in GIS e.g. impedance and reactance for transformer etc. With separate field for each classification and sub-classification will easily create a table with more than 250 fields, not a best option from DBA point of view or we have to use generic fields such as "AttributeA", "AttributeB" etc. and then give alias (configure pop-up) in each layer which is time consuming exercise in the least not mentioning the heartache it gives to a data modeler. 

It seems that Asset Package 3.x has tried to address the issue by creating related tables for devices and line wires. But it is still only two related tables. Will it be better to create a related table for each subtype e.g. Fuse Unit, Transformer Unit, Switch Unit, HV Conductor Wire etc. This way Device and Line feature class only stores network attributes and symbology attributes and individual tables stores electrical and EAM attributes for that type. It seems doable but have following questions;

Can related tables participate in defining tracing? E.g. stop a trace if transformer KVA rating exceeds certain value or sum impedance of all lines traced. 

Does UN editing support adding attributes to related tables? 

Can data in related table be updated in client-server mode instead of Web Service mode?

Can "Export Subnetwork" export attributes from a related table?

All comments and suggestions welcome...

Thanks,

Vish

[AngelaDeegan]

@JohnAlsup  I would think that field re-use would make scripting (and potentially even tasks using the GUI) very challenging. I say that because we wouldn't be able to rely on the generic internal field name to know what the data in that field represents. Is that a valid concern?

[JohnAlsup]

in my opinion, fields in the UN should only ever be viewed in the context of the subtype anyway.  In fact, that rule should really apply to any table with subtypes.  We demonstrated field reuse to help reduce the total number of attributes on any given table/class.  More fields will have an impact on performance.  How much, we have not documented, but everything comes with a price.