The ORM Foundation

For the first time, I have to model data that is nothing more than a collection of statistics. The issue is that the same set of data are kept by category, for several categories. For example, for each category, I have to record quantity 1, quantity 2, and quantity 3. For each entity, I have to record the three quantities for category A, category B and category C. Here's my first cut at a model for a single entity:

This model makes me nervous. I don't really like introducing OneCategory, since its only purpose is to ensure that each category gets the same quantities recorded. I also don't like they way this maps to the relational model:

I was thinking more in terms of all the statistics residing in the "AllCategories" table.

Any thoughts? Are there some standard techniques for modeling this sort of data? I don't recall seeing an example of this in the Brown Book. Any thoughts on what I could improve would be very welcome.

Hi John,

It sounds like you need some ternaries, or a nested m:n fact type relating the MeasuredEntity and Category object types. But from your description, it's hard to know for sure. Any way you could give us some verbalized fact samples, or at least a data use case (e.g. output report or query result)? That would be the easiest way to tell.

Regards,

Andy

This is attempting to model something like the following:

Each entity needs to enter data on all quantities, in each of the three cagegories.

Hi John,

Thanks for the data use cases. The most straightforward way to model this would be as a single quaternary fact type, something like:

This would map to a single relational table. However, numeric values for different entity-category-quantity combinations would appear on different rows. If you want all the values for a given entity-category combination to appear on the same row, you could do a transform of the above schema to the following:

Or, make the rows correspond to an entity-quantity combination, instead:

Hope this helps.

Regards, Andy

Thanks for the reply. You gave me some things to think about. In particular, I need to ask the Domain Experts whether the database needs to maintain named quantities and/or named categories. It's possible that, as far as the database is concerned, a simple "array" structure would work, "indexed" on CategoryCode and QuantiyNr.

In the meantime, do you know of any way to get NORMA to "absorb" all of the "OneCategory" instances into the "AllCategories" table? I could do it through a view, but i wonder if there's a way to do it through the model.

John Saunders:

In the meantime, do you know of any way to get NORMA to "absorb" all of the "OneCategory" instances into the "AllCategories" table? I could do it through a view, but i wonder if there's a way to do it through the model.

Unfortunately, that didn't work:

That gives the same relational mapping as before.

In addition, the Id reference mode is an autocounter, and must be unique in the table where it's allocated. NORMA doesn't apply this mapping rule, though ActiveFacts does.

If you want to get rid of the NFNF problem you can add a preferred identifier as in the attached diagram.

I'm not actually sure that this model makes sense anyway. It would be much better if you shows a real example instead if a dummy one.

The problem is that this is very close to the actual requirements. A simpler model which also meets the requirements is:

This leads to the relational schema:

(this may clarify my question on absorbtion)

There is a pattern: every category contains the same quantities. Every "Quantity1" is of the same type (integer, decimal, duration, etc), as is every "Quantity2", etc. I'd like to take advantage of that in the model. In particular, future evolution of the model will no doubt involve adding or removing quantities from every category, and adding or removing entire categories. It will not be the case that a single quantity is added to a single category, for instance.

As I said, I have no experience in modeling data that is just a bunch of numbers.