ORM and Antipatterns

[Wouter Oet]

Hi Bill,

Does using an ORM such as Hibernate encourage the use of antipatterns? Or does the object oriented query approach helps reduce the number of antipatterns?

Kind regards,
Wouter

[Bill Karwin]

Hi Wouter,

Keep in mind that the value of ORM tools is to improve developer productivity, not to produce good SQL code. In a project with budget or time constraints (same thing, really), the largest expense is paying developers' wages, so ORM tools are successful if they reduce development time. That's kind of orthogonal with respect to SQL antipatterns.

But I can think of at least one antipattern that Hibernate is guilty of: Polymorphic Associations. This encourages developers to design tables where a foreign key can reference any of several parent tables. But to achieve this, you have to forgo using a foreign key constraint to enforce the references. That should be a red flag right there that Polymorphic Associations violates database design principles.

[Wouter Oet]

Thank you for your fast response and taking the time to answer questions from us.

// Edit: typo

[Gerardo Tasistro]

Bill Karwin wrote:
But I can think of at least one antipattern that Hibernate is guilty of: Polymorphic Associations. This encourages developers to design tables where a foreign key can reference any of several parent tables. But to achieve this, you have to forgo using a foreign key constraint to enforce the references. That should be a red flag right there that Polymorphic Associations violates database design principles.

You can still use only one table to store all classes. A column is added that identifies the type and thus allows the foreign key to reference only one table and not many as is the case of one table per class.

[Bill Karwin]

Gerardo Tasistro wrote:A column is added that identifies the type and thus allows the foreign key to reference only one table and not many as is the case of one table per class.

Yes, that's the common way to employ polymorphic associations: add another column for the "type" of entity you're referencing. For example:

CREATE TABLE Comments (
  comment_id INT PRIMARY KEY,
  entity_type VARCHAR(10) CHECK (entity_type IN ('Articles', 'Photos', 'Videos')),
  entity_id INT NOT NULL,
  FOREIGN KEY (entity_id) REFERENCES ...what goes here...?
);

The problem is that you can't declare a foreign key constraint to enforce referential integrity for polymorphic associations. A foreign key must reference exactly one parent table. So the only way to use polymorphic associations is to forgo referential integrity.

When we try to use an RDBMS in a way that makes it impossible to use a fundamental feature like referential integrity, this is a strong antipattern smell.

[Gerardo Tasistro]

Bill Karwin wrote:

Gerardo Tasistro wrote:A column is added that identifies the type and thus allows the foreign key to reference only one table and not many as is the case of one table per class.

Yes, that's the common way to employ polymorphic associations: add another column for the "type" of entity you're referencing. For example:

  FOREIGN KEY (entity_id) REFERENCES ...what goes here...?
);

The problem is that you can't declare a foreign key constraint to enforce referential integrity for polymorphic associations.

Example of MySQL database structure generated by Hibernate for a polymorphic representation of discounts in an application. There are various types of discounts that are represented in one table. Thus allowing many classes to reside on the same table and be referenced.

Personally I'm no fan of one table per class approach due to the reasons you mention. But using polymorphism on one table is very viable without breaking constraints.

 

--
-- Table structure for table `tokens`
--

CREATE TABLE IF NOT EXISTS `tokens` (
  `DTYPE` varchar(31) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assignedStatus` int(11) DEFAULT NULL,
  `discountAmount` double DEFAULT NULL,
  `discountType` int(11) DEFAULT NULL,
  `eventId` int(11) DEFAULT NULL,
  `lockToDate` datetime NOT NULL,
  `paymentStatus` int(11) DEFAULT NULL,
  `token` varchar(255) DEFAULT NULL,
... bla bla bla ...

  PRIMARY KEY (`id`),
  KEY `token` (`token`),
  KEY `FKCC40859AFA1A3B88` (`salePoint`),
  KEY `FKCC40859AE8FC2725` (`eventId`),
  KEY `FKCC40859A2A1CD437` (`registrationId`),
  KEY `FKCC40859A67876F2F` (`categoryBlockId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
   
--
-- Constraints for table `tokens`
--
ALTER TABLE `tokens`
  ADD CONSTRAINT `FKCC40859A2A1CD437` FOREIGN KEY (`registrationId`) REFERENCES `registration` (`id`),
  ADD CONSTRAINT `FKCC40859A67876F2F` FOREIGN KEY (`categoryBlockId`) REFERENCES `categoryBlocks` (`id`),
  ADD CONSTRAINT `FKCC40859AE8FC2725` FOREIGN KEY (`eventId`) REFERENCES `events` (`id`),
  ADD CONSTRAINT `FKCC40859AFA1A3B88` FOREIGN KEY (`salePoint`) REFERENCES `salePoints` (`id`);

[Bill Karwin]

I admit I may have misunderstood Hibernate, it appears that it transparently creates a table to serve as a supertype. Mea culpa!
http://docs.jboss.org/hibernate/stable/core/reference/en/html/inheritance.html

The antipattern is when a child table references several parent tables using a single foreign key column, but there is no table for the supertype common to all the parents. It's more common for programmers who use loosely typed languages like PHP or Ruby to try to use the foreign key in the spirit of "duck typing."