I'm looking at file upload gems and there seems to be a tendency to put all assets in single "Assets" table and using STI to subclass them. Like ImageAsset, VideoAsset, AudioAsset, etc.
I'm new to Rails and I've never used STI. Previously I would have just made images, videos, audios separate tables. Granted they may share a few columns but I'd say they'll also have some different ones (sample rate doesn't apply to image, e.g.)
Pros of stuffing all this in one "assets" table: easier to run queries on all assets. Cons: table is going to get bigger quicker. I guess I could always shard on the "type" column if that's a problem. Also I anticipate all the audio-only columns will be null on image rows, etc.
My whole app is going to be based around assets so I want to make sure I have the pros and cons straight before making a decision. Has anyone done STI assets and regretted it? Has anyone done it and not regretted it (for a large amount of assets)? Would CTI (class-table inheritance) be a better solution here?
I'd say that one of the biggest cons of using STI is that if you ever add a column to the table that is not shared (and that it means the same thing) between ALL models of the STI, then you've just blown your data integrity right there.
Null fields in (relational) databases are something that generally causes more problems than they solve.
I've beene bitten by this a couple of times, and it gets especially frustrating when the classes in your STI relation start having subclasses of their own, which in turn add even more columns to the table.
I'd say if you want to make this structure as good as possible, I really think CTI is a much better alternative, even though it can be a bit tricky to get it to work with rails. It's a lot trickier than STI at least.
At the top of my head I can just come to think of one scenario where STI might actually be reasonable, and that's when you're dealing with transaction models (for example deposits and withdrawals from a bank account or so). In those cases, both models are essentially the same, apart from the "direction" of the transaction.
It can also be argued that STI is "good" for fast prototyping, and if you just want to put something together fast just to see whether it works at all, you could use STI, but as soon as you start adding columns that doesn't make sense to all models in the relation, you should probably refactor it into CTI or something else.