Rails Database Design for Multiple Associated Record Types

Question

This is a design question. I hope that is acceptable here.

Back in the days of yore, I worked on an accounting system that used flat files to store records in a proprietary format. The transaction 'table' stored multiple types of transactions in the format of a fixed header (which held the transaction type amongst other things) and a detail record that varied according to the transaction type.

We did this in C using structs and unions to manage the records.

Once again I find myself working on a finance system, only this time I'm doing it in Rails with PostgreSQL as the target database.

I'm currently considering the design of the transaction system in this new project and I'm wondering how to do this in the best, Rails way.

• Do I have multiple tables, one for each transaction type: journals, sales, purchases, etc? (Does this violate DRY?)

• Or, do I have a single transaction table that represents the header, and multiple tables for the detail of each type?

  If I do it this way, how do associations work? Can an association use a single id to connection to multiple tables?

• Or, do I have a single, un-normalized record with every possible field for the complete range of transaction types?

• Or... is it possible to overload a table record in the way I did this so many years ago?

(Not really keen to do it this way though.)

Any ideas you have on this will be appreciated.

Answer 1

Option 1 sounds like you'd need a polymorphic association, which is supported by Rails out of the box. Although it works the other way around normally - The usual use-case is along the lines of a comment object that can belong to multiple content types. There's a gist here that may or may not help: gist.github.com/runemadsen/1242485

Although the gist uses a join table on which you can add common fields, which starts to sound like Multiple Table Inheritance, your second choice. This is a good solution. You'll need a gem to make it work with Rails though. Try this one:

github.com/hzamani/active_record-acts_as

Option 3 sounds like single table inheritance which is generally a bad move.

I'm not sure how 4 differs from 2.

I would err on the side of 2: Multiple Table Inheritance. Granted, if a polymorphic association works for you it's nice to be just using Rails native features, but I think you'd find it would get more and more annoying to deal with over time. Simple things like counting all transactions or getting all transactions within a date range would become arduous and fragile. MTI is a pattern created exactly for this kind of situation.