SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Synopsis: Polymorphic Associations

Synopsis: Polymorphic Associations

Let’s look at an antipattern that originates from multiple inheritance relationships.

We'll cover the following...

Let’s allow users to make comments on bugs. A given bug may have many comments, but any given comment must pertain to a single bug. So, there’s a one-to-many relationship between Bugs and Comments. The Entity-Relationship Diagram for this kind of simple association is shown below.

The following SQL shows how we would create this table:

MySQL
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  bug_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES Accounts(account_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

However, we may have two tables we can comment on. Bugs and FeatureRequests are similar entities, although we may store them as separate tables (see Concrete ...