What to use instead of Polymorphic Associations?

Question

So for a number of reasons Polymorphic Associations are considered bad database design, e.g. you can't have foreign keys on the polymorphic id columns so referential integrity is gone.

And, STI is also considered bad unless the sub-types only differ on behavior.

So my question is for Rails / ActiveRecord what is the lesser evil in the following scenario:

I need to allow my users to create a hand-ordered collection of multiple types of entities.

Using Polymorphic Associations the schema would look something like this:

-- collections
id, name, ...

-- exhibits
id, collection_id, exhibitable_type, exhibitable_id, position

-- photographs
id, ...

-- films
id, ...

-- paintings
id, ...

-- songs
id, ...

-- sculptures
id, ...

I could use STI and have something like this:

-- collections
id, name, ...

-- exhibits
id, collection_id, artwork_id, position

-- artworks
id, type, <photograph columns>, <film columns>, <painting columns>, etc.

But, because my artworks differ on data (and not just behavior), I now have NULLs all over the artworks table. I've also introduced an inheritance hierarchy to my objects which previously wasn't there and I am generally wary of, particularly when its only purpose is to serve the database design.

STI does seem like it would at least be simpler to query and code for.

So which is the lesser evil?

Then, the other option is multi-table inheritance:

-- collections
id, name, ...

-- exhibits
id, collection_id, artwork_id, position

-- artworks
id

-- photographs
artwork_id, <photograph columns>

-- films
artwork_id, <film columns>

This would get rid of STI's NULL problem and also keep the table sizes down. We still have object hierarchy that I don't think is strictly necessary. But the biggest problem in my eyes: Rails doesn't support it. The CITIER gem looks very nice and it has recent commits but it is not that widely used so I am cautious of using it in my app's foundation. I guess I could also look at using Sequel or another ORM that has CTI support.

Truth be told I think I like the multi-table inheritance solution the best

If I didn't need the position column I would simply use separate join tables like collections_photographs, collections_paintings, collections_films, etc. but I do need that manual ordering.

So, it seems the options are:

• PA: loss of referential integrity (does it matter in practice if always using ActiveRecord for database access?)
• STI: big table with lots of nulls
• CTI/MTI: relying on a gem that isn't widely used

Which is the lesser evil and are there any other options? I am using Postgres.

Answer 1

Another option is to fully normalize your database structure and to have views to transform the data into a form that is more suitable for queries. I'm not sure how well this would work with different ORMs, so your mileage may vary. In general, I've found it is best to base your data model on how the data is genuinely structured since that tends to lend itself to ease of maintenance. After the data has been modeled properly, you can then create views and stored procedures to access and manipulate the data conveniently.