How to store multiple Static Entities on one Entity?
Question
Application Type
Reactive
Service Studio Version
11.53.9 (Build 61018)

I am wondering if there are recommendations for the following scenario. I am building an app with Entities that have coded values, or in OutSystems lingo, Static Entities. More precisely, I have TableA which has columns Id, AddedOn, AddedBy, SampleTypeCode, and SourceCode. SampleTypeCode and SourceCode are attributes each of which correspond to a Static Entity. For instance, SampleTypeCode could include the options "Hair", "CSF", and "Urine". SourceCode could include the options "Internal", "External", or "Other". 

I have TableB as well, which has similar columns, namely, Id, AddedOn, AddedBy, SampleTypeCode, UsedByCode, and StatusCode. TableB.SampleTypeCode = TableA.SampleTypeCode, but the other two code attributes (UsedByCode and StatusCode) would each have their own options/enumeration. 

Say I have a LOT of these code type fields, some of which are shared by multiple tables, others unique to a single table. I do not really want to have a Static Entity for every single one of these code sets. I would much prefer to have an Entity (say it's called CodeValue) that lists ALL the values for all of these Static Entities. CodeValue has the columns Id, CodeValue, and CodeTypeId. CodeValue.Id = the unique value for the given code that is written on TableA or TableB (e.g. TableA.SampleTypeCode might record a "5" for "Hair"). CodeValue.CodeValue = the actual text value, in this case "Hair". CodeValue.CodeTypeId is a FK that goes to CodeType table, which includes the attributes Id and CodeType. The CodeType is simply the kind of code it is, e.g. "Sample Type" or "Source" or "Used By", etc.

How can I create this kind of table in OutSystems, if possible? I would much rather consolidate many Static Entities into one of these code tables.

An example ER diagram:

I have searched the forum and not finding anything particularly relevant, however, it seems like I may need to have all these Static Entities after all, along with a Junction table. Alternatively, I was exploring the option of having a Static Entity containing "child" Static Entities.

Thank you!

Hi edoo,

see attached oml for an example with static entities.  Game1 is with separate static entities for each property, Game2 has all possible values in a single static entity.

I'm a bit done with this now, so no example for regular entities, but I imagine that would be with the property table still a static, but the property value table a regular one, meaning you can't invent new properties on the fly, but users can easily add new allowed values.  As long as the made choices are stored in an explicit/discrete way (explicit reference attribute in game entity) there is no point in making property configurable in runtime, I think.

Most stuff would probably be similar, for regular entities, except you wouldn't be able to code the example business rule.

So the 2 provisions i added to deal with the fact that the distinction between the types is a bit lost in the datamodel, is extra validation action ValidatePropertyValueIsOfProperty and webblock PropertyDropdown.

This kind of approach is only suitable if the chosen options are not much more than that, a choice from a discrete set of options that you want to record.  As soon as you want to write a lot of business logic with them, or you want to make all kinds of behaviour of your system depend on them, then single statics work better.  You can then for example configure the system behaviour through extra attributes in the relevant static, and if all are in a single static table, that's less possible.

Dorine

QDR_StaticEntityAlternatives.oml

Thank you, Dorine. I have not fully reviewed your solution, but it appears to work as you describe. Thank you so much for walking me through this functionality.

Hi edoo,

what you describe is a common pattern / solution to deal with large amounts of code / value pairs (from a database perspective) in any software stack, not necessarily Outsystems.  

There is typically the trade-off between less development and maintenance (such as admin functionality to update available codes only to be developed once) of making one general code/value table, versus better readability of your code and your datamodel when making a code/value entity for each of the types of things you want to codify.

In Outsystems, static entities have more similarity with the second option, as far as datamodelling is concerned, i.e. it is directly clear from your datamodel what the possible values are for a given attribute.  But static entities are much more than that, they offer a way to easily, and human readably, make reference to the possible values in code without hardcoding their value, it allows for findability if you want to know in what parts of your code a given value is explicitly used, etc.  So you shouldn't think of static entities as merely a code value table.

I think also there is a lot of value in showing the real complexity of your system in your datamodel, if you happen to be so lucky as to be allowed to work on a very complex system with dozens of coded attributes that your software has to do interesting stuff with, it is a good thing that this complexity is visible by the complexity of your datamodel picture, don't hide it !

If you are hell bent on sticking everything in a single table, you always have the option of not using static entities at all for this, but just use regular entities (as you would do in other stack, probably)  If you make some smart wrappers / getters, this could be a good solution.

When you want it as statics, then I don't see the advantage of trying to push everything in one table.  Statics don't need any maintaining so where is the downside to having many of them, and you will have to take into consideration what subset of them you want, every single time you use them.  The only thing it would do for you, is declutter your datamodel diagrams, maybe ?  

If I would want to do this with statics, I would codify the name of the static something like type_value, I don't think you need a separate foreign key to a type.  EDIT : I do see the point of having a type, as a means to select all values of a given subset. And I would make dedicated getters retrieving each subset.  But you are still stuck with for example making sure that only foreign keys of the correct type are created.

Bear in mind if making this type of choices, that other people will also work on your software, and having to explain it is a big disadvantage over just using things that everybody already knows and uses, like the 'normal' way of using statics.

One single consideration PRO, maybe statics count as application objects, so can be costly to have many of them.

Dorine

Thank you for unpacking all the options, Dorine! I see your point about simply having Static Entities, even if they do clutter things up, because they are what everyone knows. In actuality, I have 6 of these, 3 of them are shared across multiple tables (up to three tables). So there really isn't so much decluttering that would occur by going with the single table option.

On the other hand, I have considered the possibility users will want to edit the code values, in which case the single table option might be better for design reasons as you mention. I think another reason to do so is future-proofing - which as you mention, maintenance is one reason to go with the single table option. To whatever extent these reasons are accurate or inaccurate, I would also say the single table option seems more elegant.

Just so I can explore this avenue fully (and I am very new to OutSystems), can you elaborate on options involving "smart wrappers / getters"? Would this involve creating certain types of Actions? Do you have an example of this that you could point me to or show here?

Ok, as you mention your users editing them, statics are off the table, as changing the set of possible values would require a new release of the software.

So for the choice between static and regular entity, I think there are 2 axes to consider :

axis 1 : how often will the values change

never : static ---------------------------------------------------------------------------------dayly : regular

axis 2 : how much business meaning do the values have (i.e. how often do you need to refer explicitely to one of the values in your logic)

never : regular -------------------------------------------------------------------------------extensively : static


As for the wrappers yes, I mean actions, I'll make a quick demo

Dorine

Axis 1 - infrequently, but not never. Likely the values will change as time goes on and users want different analytical categories for examining volume and TAT through the lens of the various code sets.

Axis 2 - Extensively. The data model I have entails users adding a "case" and every time a case is added, coded values are associated. There are "sub-cases" as well, which additionally have associated coded values. The center of the app / data model rests on adding, managing, and extending "cases".

So it seems like a Static Entity is the way to go? That being said, I am all ears about the regular entity option as well.

Hi edoo,

see attached oml for an example with static entities.  Game1 is with separate static entities for each property, Game2 has all possible values in a single static entity.

I'm a bit done with this now, so no example for regular entities, but I imagine that would be with the property table still a static, but the property value table a regular one, meaning you can't invent new properties on the fly, but users can easily add new allowed values.  As long as the made choices are stored in an explicit/discrete way (explicit reference attribute in game entity) there is no point in making property configurable in runtime, I think.

Most stuff would probably be similar, for regular entities, except you wouldn't be able to code the example business rule.

So the 2 provisions i added to deal with the fact that the distinction between the types is a bit lost in the datamodel, is extra validation action ValidatePropertyValueIsOfProperty and webblock PropertyDropdown.

This kind of approach is only suitable if the chosen options are not much more than that, a choice from a discrete set of options that you want to record.  As soon as you want to write a lot of business logic with them, or you want to make all kinds of behaviour of your system depend on them, then single statics work better.  You can then for example configure the system behaviour through extra attributes in the relevant static, and if all are in a single static table, that's less possible.

Dorine

QDR_StaticEntityAlternatives.oml

Thank you, Dorine. I have not fully reviewed your solution, but it appears to work as you describe. Thank you so much for walking me through this functionality.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.