in

The ORM Foundation

Get the facts!

How to Model Categorized Statistics?

Last post 10-04-2010 13:18 by Anonymous. 9 replies.
Page 1 of 1 (10 items)
Sort Posts: Previous Next
  • 09-29-2010 19:58

    How to Model Categorized Statistics?

    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:

    Basic model - fist cut

    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:

    Simple Relational Mapping

    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.

  • 09-29-2010 21:32 In reply to

    Re: How to Model Categorized Statistics?

    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

  • 09-30-2010 14:24 In reply to

    Re: How to Model Categorized Statistics?

    This is attempting to model something like the following:

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

  • 09-30-2010 21:53 In reply to

    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


  • 10-01-2010 16:18 In reply to

    Re: How to Model Categorized Statistics?

    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.

  • 10-01-2010 20:00 In reply to

    Re: How to Model Categorized Statistics?

    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.
    Add a mandatory on the other role. OneCategory can't be absorbed through a non-mandatory relationship.
  • 10-01-2010 20:18 In reply to

    Re: How to Model Categorized Statistics?

    Unfortunately, that didn't work:

    That gives the same relational mapping as before.

  • 10-02-2010 0:14 In reply to

    Re: How to Model Categorized Statistics?

    Deleted - double post.
  • 10-02-2010 0:15 In reply to

    Re: How to Model Categorized Statistics?

    Oh, you have a normalization problem. Because the same OneCategory can be recorded in more than one AllCategory, if it was absorbed, the Quantity[123] would be recorded twice, which is not 1NF.

    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.


  • 10-04-2010 13:18 In reply to

    Re: How to Model Categorized Statistics?

    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.

Page 1 of 1 (10 items)
© 2008-2014 The ORM Foundation: A UK not-for-profit organisation -------------- Terms of Service