Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Synopsis: Polymorphic Associations

Learn to recognize the polymorphic associations antipattern and understand why it poses risks to database integrity. This lesson helps you explore how one child table can reference multiple parent tables and the complications that arise. Discover safer alternatives and how frameworks like Hibernate can manage these associations effectively, reinforcing best practices in SQL logical design.

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 ...