Cross model relationships?

[cbility]

Hello,

I'm using NORMA as a design tool for database schemas, mostly because of the constraint visualisation and verbalisation tools. I'm using the code generation feature to create a Postgres schema from a model.

I would like to be able to manage multiple schemas using NORMA because of the benefits to permission and access management and separation of concerns. Especially for larger databases it would be useful to be able to model cross schema relationships in NORMA. Currently I can create multiple schemas and manage them separately using NORMA (via independent models) but I can't see a way to create cross schema (or cross model) relationships between entities in different models.

Am I missing a way to do this? If not - do you think this is a viable feature request?

[mcurland]

Hello Adam,

In the current implementation, you are correct that there is no way to do this. You get exactly one schema per model.

So, there are two questions:

1. What can be done now so I can come reasonably close?
2. What can be done in the future to remedy this?

Things to try now
You could experiment with something like this, especially if the touch points between the schemes are limited.

For general cross-model work you want to keep a clear model dependency (informally, since the tool doesn't). NORMA does allow cross-model drag operations. Cross-model drags are pattern-based with no federation pattern (keeping the id from both sides), so things like object-type name change, reading or arity changes on fact types, etc. will fail to match items on a subsequent merge. Once you're aware of the limitations, though, you can use groups in the source model (Groups, add group) to track a subset of objects. When you drag a group to a different model (just drop it on a diagram, then see it in the model browser), you get all elements in that group. Object type elements will expand and automatically bring supertypes and identification schemes to the new model.

So, the approach so far is to put the 'external' elements you want from the referenced model in a group in that model and drag them into your model. As these are minimal objects you will likely need to force them to be independent so they get tables, which forces the other code to reference them and producing foreign keys (instead of simply absorbing the id data). You also want to make sure that the naming conventions match. Now, go into the abbreviations dialog and modify the target names for the relational target in a consistent and searchable way. So, ExternalType becomes xschemaExternalType.

Now, when you generate, you'll have DDL than can be easily searched and edited. Delete the y.sxchema tables, and change references to y.xschema to 'x.'.

This isn't perfect. NORMA has internals that allow chaining of the generation, so you could potentially automate this. The problem is that when VS went to side-by-side it became much harder to actually install these generators. I could probably come up with instructions and an example fairly easily (my old example added audit columns to all generated tables so things like author could be tracked automatically).

Possible Futures
When you're talking about splitting up schemas, you're generally dealing with a large model. The one allowance the Pro tool makes for this right now is allowing the relational diagrams to be spread across multiple pages (the all-on-one-page is pretty much useless after a couple dozen tables). This would likely be in that environment as well, especially given the performance issues on large models with the current relational mapping (which fully regenerates on any significant model change).

The other finished work in Pro is hierarchies and XML schema generators. Hierarchies can be defined on a partial model and produce multiple XSD outputs. Think of a partial model as a tracked cross-model drag: you specify the primary items you want, then we expand to get related items based on settings (you always get ids patterns and supertypes, but things like mandatory and functional roles are optional). When you provide a set of primary elements in partial model group, you are guaranteed that the expanded set of elements is a valid model (assuming the containing model is valid).

So, what is needed for multiple-schema and cross-model support:

1. The set of base classes used for the hierarchy need to be mapped to relational as well. The base classes track absorptions (basically, what become value columns with no foreign key in the database schema) and then rely on the supertype to organize the non-absorbed objects and fact types. The hierarchical mappings is actually a much more complicated 'element organization' than relational, which simply maps remaining objects to tables (or absorbed 1-1s like subtypes), produces foreign keys for n-to-one and join tables for many-to-many.
2. The current partials need to have the ability to reference other partials (in an acyclic fashion). This would tell the partials to reference any object in a target but not expand (automatically or manually) past the identifier. An external reference would then be generated to target the referenced schema instead of just using a value.
3. We need a proper federation system to track cross model. The pattern matches are not sufficient to do changes that break the match, and they don't cover deletions. I've though on this some and could probably track with 3 group types: a group type with all model (implicitly populated, would have to be recognized by core group code), a group type for an external source (might be the full model group itself), and a group type for an imported model (federates the internal and external id). The federated ids would allow deletions and external updates. Basically, this is acknowledging that a simple property like 'IsExternal' is not sufficient information--need to know where the element provenance.
4. You would then be able to specify different generation targets (MODEL.x.sql, MODEL.y.sql, etc.) for each schema.

This approach would allow multiple schemas per model, as well as a formal import of a referenced model--along with its schema info--to allow cross-model references. Note that the absorption base classes are incremental (local regeneration, minimal id changes), so are much more appropriate for use in large models.

Obviously, this is a lot of work. It affects the core grouping code (to allow a virtual 'all' group), partial models algorithms (to allow references), and new group concepts for export source/import target tracking. This also opens up the hierarchies, which would need to consider dependencies hierarchies in the partial groups and modify the hierarchies and generated schemas accordingly.

Having said that, I fully agree that it would be a valuable addition to the tool.