The "Publications Example" (or "library example") is based on an example in BertrandMeyer's OOSC2 book. It has been a contentious example on usenet also. I write about it here:

http://www.geocities.com/tablizer/lib.htm  (NoteAboutGeocities)

The primary issue is that there are multiple "subtypes" of publications (book, periodical, journal, etc.) Different publications use different columns, but many of them overlap.

As far as how to design the schema for it, one approach is to create a separate table for each publication sub-type. Those that don't like empty cells ("nulls") generally prefer this approach. 

But my favorite is to have a "full" table with all possible columns, and then some kind of DataDictionary to select which publication subtypes use which columns.

One possible DataDictionary could resemble:

  table: pubColumns
  ----------
  pubType
  columName
  isRequired  // Boolean

Non-used fields would not have an entry here. Thus, for any given "field", the selections per subtype would in effect be:

* Not used (not shown on data-entry form)
* Optional (data-entry slot exists for it, but a value is not required)
* Required

One could also lay out the dictionary like the first table example shown in the above link. (It only shows "X", but to fit the above, we'd have to add something to distinguish between optional or required.) Here's a short-hand version of the pattern:

  pubType|A|B|C|D|etc...
  ----------------------
  ....1..|R|.|.|T|  
  ....2..|.|T|.|T|
  ....3..|T|.|R|R|
  Etc...
  ----------------------
  // dot=none, R=required, T=optional
  // Letters are possible columns and digits are publication types
(Dots to prevent TabMunging)
  
The problem with multiple "subtype" tables is that you have to constantly alter the schema to add new columns (such as changing from not-used to "optional"), and have to add new tables for any new publication types (like say DVD's or USB drives). It also fills up the table-space with sub-entity-level tables. 

It would also make most queries that are not specific to a single given subtype simpler, such as "show all publications entered between June and August" or "show all publications with 'plastic' in the title". These would be common. One could make a big UNION view to emulate one table, but this is yet more things that have to be changed when new pub types are added. And slower. 

The DataDictionary approach can even allow '''non-programmer''' power-users to add or change column features (if already part of the master table) and new pub types. Fetching a techie for every little change can be annoying and costly and disruptive and delaying. As techies, we sometimes forgot that what it feels like to have to pay us.
-----
The example was originally designed to show the alleged advantages of OOP polymorphism over case statements, but because of the DataDictionary, we don't need case statements.
--------
CategoryExample