ER/Data modelling (Link between inherited types

Would it be possible (or is it illegal for any reason) to have something like this:

This is basically an ISA class with a foreign-key relationship to itself.

The idea is that an output message is just simply a message. But every message (or let’s call it input message) has an associated output message. This is the need for the foreign key 开发者_StackOverflow社区relationship.

Is this ok, if not why not? Or is there a better way to model this?

Thnx.

"Output Message" is really a specialization of "Message". If some other kind of message were included, like "Input Message", the case would become more interesting. "isa" relationships with multiple alternatives lend themselves well to Object Oriented description, using class inheritance.

There are three major ways to design relations to mimic the pattern provided by class inheritance. There's the single table, with NULLs inserted in all attributes that do not pertain to the case at hand. There's the class table inheritance model, where each subclass has a table of its own that "extends" (using the word loosely) the main superclass table with a copy of the key and additional columns. The third method is sort of a compromise.

The copy of the key is both a PK and an FK that references the superclass table. Propagating the value from the superclass table to the subclass tables is done programmatically. Views based on simple joins are enough to provide you with all the data about a given subclass item, regardless of whether this data comes from the supeclass table or the subclass table.