3 ms·
> I wonder what kind of problem could possibly require so many tables. There are three main factors behind this, none of which are bad schema design: 1. Table
by derefr 7y ago
> I wonder what kind of problem could possibly require so many tables.
There are three main factors behind this, none of which are bad schema design:
1. Table partitioning/sharding. RDBMS storage engines don't tend to scale for common hybrid access patterns (e.g. contended inserts+reads on tables with many indices and uniqueness constraints) past a billion rows or so. You can scale them yourself, though, by splitting a table's dataset into multiple tables (all with the same schema) and getting the RDBMS to direct reads/writes appropriately.
2. ETL ingest tables for separate datasources. If you have e.g. 100 different scrapers for various APIs used in your data warehouse, all writing into your DB, then even if they all are writing data with the same schema, it's operationally very challenging to have them write to the same physical table. Much simpler to have them write into their own staging tables, which can have their own exclusive-access locks, get their own deduping, get TRUNCATEd separately between batch loads, etc. They can copy into a shared table, but this step is often unnecessary and skipped.
3. Matviews built for extremely expensive queries that exist for running specific reports against. These are effectively materializataions of subqueries shared by common customer queries. As such, there might be hundreds or thousands of these.
In the end, none of these are really tables ("relations", in the relational-algebra sense); they're all operational implementation details. Partitions are literally "parts" of larger tables; ETL ingest staging tables are distinct "inboxes" to a shared table; and reporting matviews are "caches" of queries. All of these things could be separate first-class object types, hidden inside the logic of the DBMS, not presented as "tables" per se, despite taking up storage space like tables. But—in most-any RDBMS I know of—they're not. So you see thousands of tables.
- mceachen 7y agoI think in the OP's case, they are staring at a dizzying number of tables for Drupal and wondering if the schema is "good." It's easy to create a clean schema for a simple problem. Drupal (and many other mature CMSes) are the result of a decade or more accumulation of features. Much like perl, "it's worse ... because people wanted it worse"[1]. 1: https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/5vPTrjRdnjY/88xwcrF5aA8J https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/...
- derefr 7y agoAh, I didn't realize people were taking the linked schema as an example of something with a lot of tables. To me, the linked schema is by no means a bad design—nor a design with a particularly large number of tables! The single problem this schema has, is nothing to do with the schema itself, but rather the way it's visualized (and possibly the way it's manipulated at the RDBMS level.) That problem is that these tables are not namespaced. Clearly, there are multiple components/microservices here. There is one set of tables for a AAA system with generic object ACLs; another set for a generic object annotations system; another set for a half-baked JIRA-like "database in the database" to handle custom CMS object-types; another set for an object store; another for a custom reverse-indexing search-engine implementation; etc. Each of these components has a sensible number of tables, and is in a practically-useful normalization form. You just can't see where each component starts and ends in this diagram, so it looks like one massive web of tables. If you draw in the component boundaries, then this schema is quite elegant, both in concept and in practice. When working with the code of any given component, you'll only be dealing with a mental model consisting of the tables related to that component, and maybe with the foreign-key references from the "generic" components that can make assertions about anything (like the tags/annotations, or the ACLs)—but you can usually forget about those and just CASCADE updates/deletes down to them. There's no feature-itis here. "Drupal" is just the name of a system containing seven or eight distinct services. Sort of like "Kubernetes" is. The only "problem", if you want to call it that, is that because Drupal is one codebase and one process, it doesn't strictly need to take advantage of the isolation features RDBMSes provide to allow distinct applications/microservices to not get in one-another's way, e.g. SCHEMA objects. Unlike something built as a bunch of standalone processes, there's no force pushing Drupal to componentize (and therefore, naturally namespace) these tables. They'd have to make an explicit choice to do so.