Cassandra Database Design – Penalties of Using Many Column Families or Keyspaces

In my experience, a recursive hierarchy is the most practical way of tackling this. It offers the following advantages:

1. Unlimited depth.
2. Compactness.
3. Flexibility.
4. Speed.

By contrast, it takes an extra table for each level of "-to-many" joins. This is hard coded and difficult to maintain against schema updates.

By using filtered indexes, a large table of hierarchical joins can perform at superior speed to dedicated tables. The reason is each join is only "parent-child" compared to "to join table to data table". The latter has more indexes to process and store.

I've been trying to solve this problem for many years. Recently, this is what I came up with.

In general, I have never found polymorphic associations to work very well. The problem is that once you open up something like this, you have to have unique identifiers across the tables and these need to be enforced. Otherwise you end up with a case where "customer has the same 'id' as an invoice and therefore ship-to information is mixed up."

The alternative is to define polymorphic association groups and use foreign keys. For example we might have a table entitled "objects" and in it we might have two columns: table_name and object_id. We can then use triggers to maintain that table and have all tables in the polymorphic set define foreign keys against this association catalog (by table name and id) and we can therefore guarantee uniqueness that way. We can then set foreign keys on the other side, and reference the catalog table. This isn't perfect If there is a trigger malfunction it is possible for a reference to be dangling, but it avoids the worst of the join projection and insert anomilies. Also depending on the complexity of the catalog, mutual foreign keys may be useful.

Finally some databases (like PostgreSQL) would allow table inheritance to be used to create something like this, but this is a rather advanced topic.....