SQL Database Design » 13. Design Heuristics » 13.4 Modeling Relationships

13.4 Modeling Relationships

Table of Contents

This section is about creating logical models with a focus on how to translate non-relational and conceptual features to relational ones. On the logical level all relationships are translated to foreign keys, which always have a degree of two and a cardinality of either one-to-one or one-to-many.

The most frequent translations from conceptual or non-relational models are many-to-many and multi-degree relationships. These can only be translated to the relational model by an intermediate "join table". There are use cases for join tables for non-many-to-many and non-multi-degree constellations, but they are rather rare. Regular join table use cases can be found in sections 13.4.2 Many-to-Many Relationships and 13.4.3 Multi-Degree Relationships.

As depicted in section 5. Properties of Relationships, relationships have several properties in common: type, degree, cardinality, optionality, and navigability. They all have to be determined to form a complete logical model. The relationship properties are the center of attention when modeling relationships. Some of these properties are easily determined whereas others require quite some thinking. Yet others depend on choices you made before. I assume you've already somewhat decided on the natural vs. artificial keys, as they are usually decided on before anything else.

I've divided this section by context rather than by relationship property, because an isolated consideration of types, cardinalities, optionalities etc. doesn't make much sense. Relationships appear in many different (modeling) contexts and have different implications for the actual SQL/DDL code. Relationship properties are somewhat predictable in most contexts, but also involve specific decisions to be made. Thus, the division by context is more appropriate.

A context can be considered a database mini-pattern. They are similar to object-oriented patterns, just not that complex. Relationship properties are orthogonal to each situation in which they appear. They all have to be determined per context. Here are some general, cross-context facts about relationship properties:

  • Degree:

    One important observation is, that degrees for referential and hierarchical relationships translate differently to logical or physical models. A referential relationship with a degree of three results in a join table with three foreign keys referencing all surrounding tables.

    For hierarchical relationships, instead of using a join table, each sub table must declare a foreign key to the super table. For a hierarchical relationship with a degree of three, this results in only two foreign keys from the sub tables (it's always one less than the degree because of the super table).

  • Type:

    The type of a relationship, that is association, aggregation, composition, and inheritance, is central when determining the other relationship properties cardinality, optionality, and navigability. The type has the greatest influence on implementing foreign keys and their referential integrity constraints.

    The type implements a hidden relationship property: the existence dependence. This property applies to compositions and inheritance only. It makes sense when thinking about it: the part or sub entities can only exist if their wholes and super entities exist. This observation is important, as it basically means an automatic forward optionality of one (mandatory).

    The type of a relationship must be known for this section's heuristics to work. While inheritance relationships are also easy to identify, the referential relationship types association, aggregation, and composition can sometimes be hard to distinguish. Whether you use aggregation in your own designs or not is up to you. If you don't use them, treat aggregations like associations.

  • Cardinality:

    Remember, foreign keys are either one-to-one or one-to-many. One-to-one and one-to-many foreign keys have only a minor declarative difference in SQL. The foreign key declarations in a CREATE TABLE statement are exactly the same, but they implement one-to-many logic by default. To implement one-to-one logic, the common technique is to declare a unique constraint (or an identifying relationship's primary key) constraint onto the foreign key:

        CREATE TABLE ...
        (
          ...
          user_id INTEGER NOT NULL UNIQUE FOREIGN KEY REFERENCES Users,
          ...
        )

    As an example, think of a car repair and tuning shop: engines and cars may be taken out of a car and others might be put in place. While cars and engines define a one-to-one aggregation, many engines will be left unassigned (in the database) after being removed from a car (at least until they are scrapped or sold). (Note, that if you were producing a database for a scrapping company, you might consider the car-engine relationship as a composition - even though it would be unclear why you should keep all this info about the parts.)

    One-to-one relationships suffer from a problem with unassigned entities: because the unique constraint allows NULLs, it allows unassigned entities. However, it doesn't allow more than one unassigned entity at a time, because (standard) SQL doesn't allow multiple NULLs on a unique column.

    If you really need more than one unassigned entity, you have a problem: you can expect a standard-SQL compatible DBMS to restrict multiple NULL values on a unique column, but DBMSs like Oracle do allow multiple nulls on a unique constraint (although this only proves that some DBMS manufacturers don't care about standards). You have two solutions in such a case: either remove the unique constraint and make sure the entities are consistent or implement a one-to-one join table. It's sometimes hard to make the right decisions. Now let me get back to cardinalities.

    To restrict the maximum number of related entities to a concrete number, the technique usually is to declare a check constraint onto the foreign key. Another possibility would be to put an extra attribute having a restricted type into the table's primary key. Here's a small example of restricting a basketball game to exactly four periods, assuming there are no overtimes:

        CREATE TABLE Periods
        (
          game_id     INTEGER NOT NULL,
          ordinal_nbr INTEGER NOT NULL CHECK (ordinal_nbr BETWEEN 1 AND 4), -- no overtimes
          ...
          PRIMARY KEY (game_id, ordinal_nbr)
        )

    As another example, to restrict to a maximum of two referencing entities, you might want to use an additional BOOLEAN in the primary key. Note though, that on MySQL a BOOLEAN is just an alias for TINYINT(1), which allows any value from 0 to 255 and consequently can't be used to restrict to two entities. As long as your DBMS isn't in the way, the shown technique ensures that the number of related entities is within its maximum bounds, which is everything cardinalities are.

  • Optionality:

    While cardinalities represent the maximum number of related entities, optionalities represent the minimum number of entities. Optionalities are generally represented by the SQL NULL keyword. In the case of related entities from other tables optionalities are represented by a NULL on foreign key columns. Optional relationships cannot be implemented via identifying relationships. Entities that have an optional number of related entities can be made visible via a SELECT statement (of course using an appropriate join to do so). Optionalities are a kind of runtime feature, which cannot be expressed through the DDL.

    Optionalities are more dependent on the type than cardinalities, which makes the type the central relationship property. Cardinalities (and degrees) are usually easy to model and often spring right at you. Optionalities are harder and less obvious overall. Like the cardinality, optionality has two sides, called forward and backward optionalities.

    A definition of which relationship end's optionality is the forward and backward one can only be given on the foreign key level. A disambiguous direction cannot be determined for relationships on the conceptual level. Because I mostly deal with logical models in this section, I define the forward optionalities as the one near the target table of a foreign key and the backward optionality as the one near the table declaring the foreign key:

    []-|o-----oE-[]
    Forward and Backward Optionalities

    Backed by the above definition, the forward optionalties are the more interesting ones. The forward optionality is an indicator for an existence dependence. Existence dependence is always given for composition and inheritance. Backward optionalities follow rules specific to the constellation at hand. More on optionalities in the sub sections.

  • Navigability:

    Navigabilities always presume a paradigm (here relational). Foreign keys can only point into one direction of the table in the REFERENCES clause of a foreign key declaration. There aren't many design constellations where you can actually choose in which table to declare the foreign key. This makes navigabilities an implicit property on the logical level and is not much of an issue in this section anymore.

To derive the actual SQL for each foreign key, you have to take all relationship properties into account. There are no simple rules that directly lead to the "correct" SQL. In the next sections, I will discuss recurring "mini patterns" in database design, that should help you find an appropriate solution for SQL.

As it turns out, the relationship properties translate to SQL in a rather complicated way. The task of the following sections is to not only determine the relationship properties, but also how these translate to SQL. Each of the sub sections ahead includes a transformation of roughly the following features:

Translation of Relationship Properties to SQL Features
Relationship PropertySQL Feature
Degree Join table
Type Existence dependence as ON DELETE CASCADE foreign key rules
Cardinality*Unique constraints (one), check constraints and bounded data types (concrete maximums)
Optionality*NULL or no related entities
NavigabilityTable of foreign key declaration

*) Cardinality and optionality can be directly implemented by identifying relationships, which basically combine unique and not-null constraints.

Note, that the above mappings are rather approximate. I will explain each context in which this transformation occurrs in this section's sub sections. Reading them enables you to find the correct relationship properties and SQL features for each design constellation. Note, that while ON DELETE and ON UPDATE exist, the focus is on the ON DELETE rules. When updating, you almost always use the CASCADE action, because it's what you usually want. The delete rules are probably the most interesting results.

The SET NULL and SET DEFAULT rules are very similar, because the both set the foreign key to a specific value, namely null or a specified default. Setting to null or a default can only be performed for association and aggregation. The CASCADE action is the "de facto" integrity rule for composition and inheritance. The NO ACTION (or RESTRICT) action simply rejects existing foreign key references from being dissolved or changed.

SET NULL cannot be used for identifying relationships, because the referencing column is part of the primary key, which cannot be null. The DBMS should generate an error in an attempt to do so (a DBMS should be able to detect SET NULL on dependent keys by plain SQL). However, I'm not sure if DBMSs are requires to generate an error at the time of the CREATE TABLE statement. The application of SET DEFAULT to identifying relationships is technically possible, although I cannot come up with any examples.

Types and cardinalities form a tabular scheme, which I will use to structure this section. The following table depicts this scheme of possible type-cardinality combinations:

Types and Cardinalities as Use Case Determinants
TypeCardinality
One-to-OneOne-to-ManyMany-to-Many
Loose Coupling,
Existence Independence
Association6.6.2., 3.
Aggregation5.5.2., 3.
Tight Coupling,
Existence Dependence
Composition4.4.n.a.
Inheritance1.n.a.n.a.

The numbers in the table represent the steps taken in the heuristic to examine database designs. The basic order is from easiest to hardest to identify design constellations. Inheritance is the easiest to identify, whereas one-to-one and one-to-many associations and aggregations are the hardest ones. The rest lies inbetween.

13.4.1 Inheritance

Inheritance relationships are basically multi-degree relationships with some specific properties and consequences. All inheritance relationships usually have a degree larger than two (a degree of two would mean only one sub table, which could again be considered a misdesign).

The properties of inheritance relationships apply to all involved foreign keys at the same time. They cannot be changed independently of each other: a change of the properties affects either all of the foreign keys or none. It wouldn't make sense otherwise. Thus, all foreign keys belonging to inheritance share the same cardinalities and optionalities.

Inheritance foreign keys always have a cardinality of one-to-one. Forward optionalities to the super table are always one (mandatory), because a sub entity is always tied to its super entity. It would otherwise break the contract between the sub and super tables. Backward optionalities are zero (optional), because a super entity doesn't necessarily mean each sub table has a corresponding entity.

The only situations where you'll meet backward optionalities set to one (mandatory) are when you require all sub tables to contain respective entities. This can only happen when you have exactly one sub table or you have multiple sub tables in a non-disjoint constellation which require all sub tables to contain entities. However, both constellations expose bad database design.

With just one sub table, you should merge it into the super table. The second case with the requirement to have all sub tables contain entities is technically the same: why should anyone bother splitting up mandatory data into several sub entities when they can all be merged into a single table? Instead of allowing such inheritance constellations, you should merge all sub tables into the super table and forget about all the "fuss" with inheritance.

Note, that, given the examples above, the total/partial and disjoint/non-disjoint properties themselves have no direct effect on the cardinality or optionality. In a partial inheritance relationship, the sub entities are always optional, resulting in an automatic backward optionality of zero. For total inheritance relationships, the super entities only exist along with sub entities, but they can't be relied upon to exist in every sub table.

You can conclude finally, that backward optionalities are basically always zero (optional) - whether total/partial or disjoint/non-disjoint. It would be best if you made all backward optionalities "optional" by default, no matter what. Designs change frequently and adding another sub table to your design then doesn't require you to adjust the backward optionalities - even in (preliminary) single sub table scenarios.

Inheritance foreign keys always result in the ON DELETE CASCADE rule. I cannot think of cases where ON DELETE NO ACTION or ON DELETE RESTRICT should apply. The ON DELETE SET NULL and ON DELETE SET DEFAULT rules are completely inapplicable in my opinion. This is also because inheritance is mostly implemented as identifying relationships. This also leads to an automatic unique constraint to ensure inheritance's one-to-one cardinality.

In relational systems, a discriminator can easily be implemented in the super table as a column with a check constraint using the IN operator or an enumerated type. As tables can't be queried for (sub) type info, you must create a separate attribute to carry this information. Note, that a discriminator column only works for disjoint hierarchical relationships.

13.4.2 Many-to-Many Relationships

The relational model has no natural mechanism to represent many-to-many relationships. Regular many-to-many relationships (with a degree of two) and relationships with a degree greater than two (of any cardinality) must be translated using a so-called "join table". Join table entities basically just represent links, with or without extra attributes.

Join tables can implement every cardinality, that is they can represent one-to-one, one-to-many, and many-to-many cardinalities. It only depends on which foreign key gets the unique constraint. One-to-one and one-to-many join tables are used in situations where the unique constraint prevents multiple unassigned entities (NULLs), as already explained in the introductory part of this section. One-to-one and one-to-many relationships as join tables are out of scope for this section. I solely focus on many-to-many relationships onward.

Because join table entities are links, you cannot let their foreign keys become null. Thus, many-to-many relationships are usually implemented as identifying relationships, that is they are translated to a join table without an extra identity key. Doing so saves you from a meaningless column and avoids duplicates without the need for an extra unique-not-null key on the otherwise "natural" key columns. It's perfect for most (if not all) many-to-many relationships unless you do not reference that join table often.

Many-to-many relationships can never be compositions. They can only be either associations or aggregations. This makes sense, because the interconnected entities cannot own each other at the same time. They are loosely coupled entities. Join tables are often hard to identify in existing designs. Many get irritated by the fact that other tables reference the join table. It doesn't matter. Join tables only define "outgoing" foreign keys. For all outgoing foreign keys, the type must be the same and so do all cardinalities.

However, the symmetry rule does not necessarily apply to optionalities. Since join table entities are just links, they should only exist as long as the interconnected entities do. This means, that the forward optionalities of the two foreign keys are always one (mandatory). This means, that if you think a table might be a join table, it likely isn't one when the forward optionalities differ. It's a pretty useful indicator. However, the symmetry rule isn't applicable to backward optionalities.

||--?E[]3?--||
Many-to-Many Relationships: Symmetry Rule

As many-to-many relationships can only be loosely coupled associations or aggregations, the backward optionalities are virtually always set to zero (optional). However, this isn't always the case: if you demand, that there must be at least one link "to the other side" you might want to set that side's backward optionality to one (mandatory). Note, that the backward optionalities are rarely implemented that way, but that you cannot generally assume symmetry.

An example are basketball rosters: while a roster has many players, players can be part of several rosters (e.g. youth players). The "TeamMembers" join table would declare two foreign keys, one to the "Players" table, and another to the "Rosters" table. A basketball game is never tipped off unless there are at least five players on the court. So you could further demand, that a roster must at least have five team members to start the season, which results in a backward optionality of one for the roster side (mandatory). If you think you have players in your database, which haven't (yet) been assigned to at least one roster, this makes the other backward optionality zero (optional).

While this is a seemingly extreme example, it demonstrates that backward optionalities in join table constellations are neither automatically zero nor symmetric. Symmetric zeros are only the most frequent case. If you don't design such strict rules, you might be well off assuming symmetric zeros for the backward optionalities (optional). Remember, join tables mostly have symmetric backward optionalities. If they aren't symmetric, chances are they might not even be join tables. You have to decide on the context at hand. It's often hard to be 100 percent sure.

Given the above facts, you could be inclined to demand symmetric foreign key delete rules for join tables. However, that's not a must as well. Join table entities don't make much sense anymore when any of the interconnected entities are deleted and should be deleted, too. In general, there's nothing bad with having ON DELETE CASCADE rules on both sides of a join table. It only means, that entities in one interconnected table would delete the join table entities, whichever table's entity comes first. While this might appear unusual at first, it isn't. This does not violate data integrity. There's no general rule whether you should avoid these symmetric cascades or not.

Join tables being associations or aggregations doesn't mean they cannot have an ON DELETE CASCADE rule. A cascading delete just has to do with the fact that join table entities are links, which don't make sense when interconnected entities are deleted. However, there are a rather equal number of cases when the one end has ON DELETE CASCADE and the other has ON DELETE NO ACTION.

13.4.3 Multi-Degree Relationships

In the relational model multi-degree relationships must be translated to an intermediate join table:

It is advantageous to remove ternary and higher order relationship types. ... It is in fact always possible to remove these high-order relationship types and replace them with an entity type. A ternary relationship type is then replaced by an entity type and three binary relationship types linking it to the entity types which were originally linked by the ternary. A quartenary relationship type would be replaced by an entity type and four relationship types and so on.

The majority of multi-degree relationships are a sub category of many-to-many relationships. Multi-degree relationships often emerge as the result of a regular, two-table many-to-many relationship when the join table is extended by another foreign key. Due to their similarities, types, cardinalities, and optionalities are treated the same way as for many-to-many join tables.

As an example, a ternary relationship implies a relationship with three different tables. You usually create an intermediate table with degree number of foreign keys to all of the related tables. Again, join tables can be implemented using identifying and non-identifying relationships, however, identifying relationships are preferred.

For one-to-one multi-degree relationships each outgoing reference gets a separate (!) unique constraint, no matter if the table uses an identifying or non-identifying (usually multi-column natural-key) relationship. For one-to-many join tables, analogous logic applies.

13.4.4 Composition

Composition implements existence dependence. Making a foreign key represent composition automatically results in an ON DELETE CASCADE. However, in the opposite direction, an ON DELETE CASCADE rule doesn't automatically mean composition. It could as well be a rule for inheritance, association, or aggregation. It's often hard to decide which foreign key actually is composition, especially when an ON DELETE CASCADE is obvious. This frequently occurrs when a foreign key involves a table which appears to be a join table.

Compositions can only be one-to-one or one-to-many cardinalities, but never many-to-many. Forward optionalities are always set to one (mandatory), because the parts depend on the whole. Backward optionality depends on the context. If the parts are optional, backward optionality is zero (optional). Because of the existence dependence on the whole, compositions are usually implemented as identifying relationships by adding an attribute to the (reused) primary key.

Parts are usually created along with their wholes, but they can also be created after the parent. Example: sports games. The game itself has two opponents, one for the home team and one for the away team, here called score. Each score is composed of the individual player stats (points, goals, etc.). The games must be inserted into the database at the beginning of the season. Games have two scores, one for each team. Because the scores are tied to the team info, the score entities must be entered into the database as well.

Every score entity is composed of the player stats. However, the player stats can't be created until the game has been played. This can be a time span of several months, if, for example, the schedule is generated at the start of the season. Even though a score is composed of the player stats, the creation of the player stats is independent of the creation of the scores.

The deletion of entities is more interesting than the creation in the context of databases anyway, because of the ON DELETE CASCADE foreign key rule, which is mostly the case for compositions. The other valid rule only is ON DELETE NO ACTION. ON DELETE NO ACTION is useful on tables on a high aggregation level. It's a safety issue. This restricts coarse-grained entities from triggering cascading deletes. You sometimes want to avoid deletion of too much data and/or you want to avoid "data holes". ON DELETE SET NULL and ON DELETE SET DEFAULT aren't applicable for compositions.

13.4.5 Aggregation

This section is only about one-to-one and one-to-many aggregations. Many-to-many aggregations have already been handled in previous sections. Aggregations are often hard to differentiate from associations. Think of aggregations as regular, loosely-coupled associations with "part-of" semantics. Keep in mind the difference is only semantic: the one table represents the whole/s and the other represents the parts. It's really not any more.

One-to-one relationships can generally be implemented in two ways: have table one reference table two or vice versa. But which way is better? The knowledge of which entities are the whole and parts determines who references who: the parts must reference the wholes. This whole-part differentiation can be considered aggregation's only practical use.

Having the parts reference the wholes doesn't require you to change your design when switching to a one-to-many cardinality, which further saves you from executing two (possibly lengthy) ALTER TABLE statements. Even if you don't plan to extend a one-to-one cardinality to one-to-many anytime in the future, the referential action logic would be reversed if the foreign key was specified in the whole: deleting or updating the engine of a car would cause the execution of referential rules, which would be strange. Sticking to the parts referencing the whole is just more natural and may prevent database anomalies.

In aggregations, the forward optionality depends on the whether parts are optional or mandatory. They are represented by an SQL NULL (optional). Backward optionality depends on the minimum number of parts the whole can have. Note, that either setting a part's foreign key to NULL or deleting the entity itself has the same effect. The nullability usually results in an ON DELETE SET NULL foreign key rule. ON DELETE CASCADE is basically never the case, because that would change one-to-one or one-to-many aggregation to composition.

Because aggregation is closer to association than to composition, it's harder to tell aggregation and association apart. You will likely encounter situations when on one day you will tend to aggregation and on the next day you think it's rather association. The difference between them is marginal. Don't bother changing your mind too often. Aggregations have no declarative differences to associations in SQL. The difference is really just semantic and won't have any effect on the SQL.

One-to-one and one-to-many associations and aggregations should never be implemented as identifying relationships. The entities in both tables may exist independently of each other, so the foreign keys should be nullable. This cannot be ensured by either primary keys or unique, not-null constraints on the foreign key.

Note finally, that aggregations virtually always apply when some kind of volatile, artificial, temporary groups are involved, such as teams, organizations, communities, basically any collections of humans or animals. It's a rule of thumb that works astonishingly well to identify aggregations (of any cardinality).

13.4.6 Association

Associations are relationships between entities that are treated equally. There's no whole-part semantics, no existence dependence, and no other property that makes it clear who has to reference who. But who references who when both entities are treated equally?

It's really hard to tell. As a rule of thumb, have the more static entity reference the more movable one. For example, the relationship between a car and a parking lot is a one-to-one association. It would make slighlty more sense if the parking lot referenced the car and not the other way around, because a parking lot can ba associated with a "consumer", but a car doesn't really need a parking lot attribute. You get the idea.

Because associations are very similar to aggregations, the same rules for the optionalities, navigabilities, and foreign key deletion rules apply. There are two special cases for associations where relationship properties can be easily determined.

The first case are tree data structures. Parent foreign keys usually represent association. It can't be composition, because the root entity can't or shouldn't have a parent, which makes that foreign key nullable. Furthermore, the idea of the parent being the whole doesn't really make sense: does a league consist of the leagues below, so that when the league on top is deleted all child leagues are deleted, too? It doesn't make any sense. Thus, parent links also aren't aggregations.

The second common use case concerns all kinds of auxiliary tables. Auxiliary table entities can always exist without other entities. Thus, the type of a foreign key always represents association. Consequently, backward optionalities are always zero (optional) for both use cases.

13.4.7 Summary

The following table sums up the previous sections in terms of relationship properties, dependence of keys, and foreign key delete rules:

Relationship Types and Cardinalities
TypeCardinality
One-to-One One-to-Many Many-to-Many
Association
  • foreign key declared in either table (← or →)
  • usually not as identifying relationship
  • extra unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • never CASCADE
    • rarely NO ACTION / RESTRICT
    • mostly SET NULL
    • rarely SET DEFAULT
  • foreign key declared in "many" table (←)
  • usually not as identifying relationship
  • no unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • never CASCADE
    • rarely NO ACTION / RESTRICT
    • mostly SET NULL
    • rarely SET DEFAULT
  • foreign keys declared in join table (↔)
  • usually as identifying relationships
  • no unique constraint on foreign keys
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rules:
    • mostly CASCADE
    • rarely NO ACTION / RESTRICT
    • never SET NULL / SET DEFAULT
Aggregation
  • foreign key declared in part table (←)
  • usually not as identifying relationship
  • extra unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • never CASCADE
    • rarely NO ACTION / RESTRICT
    • mostly SET NULL
    • rarely SET DEFAULT
  • foreign key declared in part table (←)
  • usually not as identifying relationship
  • no unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • never CASCADE
    • rarely NO ACTION / RESTRICT
    • mostly SET NULL
    • rarely SET DEFAULT
  • foreign keys declared in join table (↔)
  • usually as identifying relationships
  • no unique constraint on foreign keys
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rules:
    • mostly CASCADE
    • rarely NO ACTION / RESTRICT
    • never SET NULL / SET DEFAULT
Composition
  • foreign key declared in part table (←)
  • usually as identifying relationship
  • extra unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • mostly CASCADE
    • rarely NO ACTION / RESTRICT
    • never SET NULL / SET DEFAULT
  • foreign key declared in part table (←)
  • usually as identifying relationship
  • no unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • mostly CASCADE
    • rarely NO ACTION / RESTRICT
    • never SET NULL / SET DEFAULT
n.a.
Inheritance
  • foreign key declared in sub table (↑)
  • usually as identifying relationship
  • automatic unique constraint on foreign key
  • fwo/bwo?: NULLs?
  • foreign key ON DELETE rule:
    • always CASCADE
    • never NO ACTION / RESTRICT
    • never SET NULL / SET DEFAULT
n.a. n.a.

References for Modeling Relationships:

Last updated: 2010-08-04