Category Archives: StackOverflow

Related to help on stackoverflow

Observation Pattern

The post relates to a stackoverflow question on DB design and my answer there.

To summarize relationships:

  • One report can list many observations, an observation can appear in many reports.
  • One subject (under observation) can undergo many observations, an observation relates to one subject only.
  • An observation is of a specific type, there can be many observations of the same type.
  • Measurement and trait are types of observations. Measurement is a numeric observation, like height. Trait is a descriptive observation, like color.

This is a simplified model based on Fowler's observation pattern, for more details see Analysis Patterns by Martin Fowler.

Organization Model

This is related to a stackoverflow question on DB design and my answer there.

To summarize relationships:

  • Customer, vendor and distributor are types of organizations.
  • One organization can have many contacts, a contact belongs to only one organization.

A few notes on tables:

  • The Organization table has columns common to all organizations.
  • The Customer, Vendor, and Distributor tables contain only specific columns for each one.
  • The primary key in the Customer table also serves as a foreign key to Organization ID; same for Vendor and Distributor.

Publication Model

This is related to a stackoverflow question on DB design and my answer there.

To summarize relationships:

  • Book, article and magazine are (sub)types of publication.
  • One publication can have many notes, a note belongs to only one publication.
  • One publication can have many authors, one author can write many publications.

Few notes on tables:

  • Publication table has columns common to all publication types.
  • Book, Article, and Magazine tables contain only specific columns for each one.
  • Primary key in the Book table also serves as a foreign key to Publication ID; same for Article and Magazine.
  • P_mm_A is a helper table for many to many relationship between Author and Publication tables.