2

I have recently started reading Martin Fowler's "Analysis Patterns" in an attempt to improve my data modelling skills and I find the book really interesting and enlightening as far as object-oriented approaches are concerned. However, although I think I understand how most of the patterns would translate into a relational database schema, I fail to see how to implement them in a realistic way.

For example, implementing the "Measurement" pattern (chapter 3.4) in a (non object-)relational database would require:

  • 1 table to store Persons.
  • 1 table to store Measurements for a Person (N-1 relationship).
  • 1 table to store the Quantity of a Measurement (1-1 relationship).
  • 1 table to store the Phenomenon Types that may characterise a Measurement (1-N relationship).

In an RDBMS all this amounts to several sort operations to realise joins and to a large number of records that need to be joined, especially if we're storing lots of Persons and lots of Measurements for each Person. I suspect it would not perform well.

The question, I suppose, is whether I am correct in my "suspicion" and if someone could point me to the right direction in terms of books or other sources that discuss the implementation of such analysis patterns to relational databases.

Thank you very much in advance, Nico

user3439878
  • 21
  • 2

2 Answers2

1

That's not complicated. From an Entity-Relationship modelling perspective, you're describing what sounds like 4 entities:

  • Person
  • Measurement
  • Quantity
  • Phenomenon Type

And a few relationships between them:

  • Each Person has zero-to-many Measurements; each Measurement measures one-and-only-one Person.
  • Each Measurement has one-and-only-one Quantity; Each Quantity is of one-and-only-one Measurement.
  • Each Measurement describes one-and-only-one Phenomenon Type; each Phenomenon Type has zero-to-many Measurements.

So...4 tables and 3 relationships in the database.

The model so described is probably incorrect, though: Assuming that he 1:1 relationship between Measurement and Quantity is mandatory — each Measurement has a single Quantity; each Quantity describes a single Measurement — is a smell.

Relationships with 1:1 cardinality are often (usually?) an indication that the two entities involved are really partial views of the same underlying logical entity. The model should be refactored to a single entity carrying all the attributes. Doing that refactoring brings you down to 3 tables and 2 relationships. Note, however, that this is not necessarily true with optional unary relationships (0:1).

Either way, that's nothing, provided the physical database design implementing the logical entity model has proper covering indices.

The thing with a properly normalized model is that you typically have many narrowish tables: narrow tables mean more rows per data page and thus faster seeks. One can always denormalize if there is an actual performance problem due to the database design. If you start with a denormalized model, once you've got a live database with gigabytes of data in it, putting into proper form gets difficult and time consuming.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • Lots of oo analysis models have to go to a Database, and the original models aren't normal-friendly. OOA heuristics often state you're not designing a database schema, but modeling real world problems. I like this answer because it says to me you just have to apply normalization techniques to those relations before they're put into tables. Don't change the analysis pattern, however. – Fuhrmanator Mar 21 '14 at 04:12
0

I do not know the book you mention. Hence, I do not understand the difference between "measurement" and "quantity". I am assuming that "Measurement" would be the related to the act of measuring, i. e. contain the date and time of the measurement, and "quantity" is the value measured, like 180 for the height of a person in centimeters.

For analysis using relational databases, a star schema is a common approach: You would have one central fact table containing the quantities, and foreign keys to the dimension tables like Date, Phenomenon, and Person.

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • The writer elaborates quite a lot on the particular pattern, so unfortunately it's hard to describe in detail, but you got it somewhat right. "Measurement" represents the act of measuring and it has a "Quantity" which is basically the value of the measurement (containing value AND unit), as well as a "Phenomenon Type" which is the type of measurement (e.g. can be 'height', 'weight', 'blood type', etc.). – user3439878 Mar 20 '14 at 00:11