6 ms·
could you elaborate on 40,000 tables DB? I want to learn what could be valid reasons for that?
by vincnetas 3y ago
could you elaborate on 40,000 tables DB? I want to learn what could be valid reasons for that?
- phyrex 3y agoRepresent every entity (like “person” or “post”) in a large graph using a relational database. You can get to 40k rather quickly
- vincnetas 3y agoIs this valid reason to use RDBM like this?
- phyrex 3y agoWhy not? I don't think there are many graph databases that are set up to handle multiple petabytes of data, so RDBMs make a good storage layer at that scale
- Timon3 3y agoDo you have any example for a project with 40k entities? I'd love to see how they handle the complexity.
- horse_dung 3y agoDatabase audit tool and they needed test what happens when an excessive number of tables is hit??? :)
- jasonjayr 3y agoNot the OP, but in our case, 300 tables x 300 customers (different 'schemas/dbs') == single mysql instance with 90000+ tables.
- vincnetas 3y agoSure multi tenant DB's, but lets limit this to per project DB's. 300 tables is quite reasonable, but multi thousands?
- wiredfool 3y agoI've got a db that hosts postgresql versions of CSVs/XLSs that are uploaded/harvested to an open data portal (as part of the portal). There are ~10k of them in there (+-), and could certainly see more (O(5k)) if some of the CSVs were parsed better.
- stef25 3y agoNever understood why each customer would have their own DB. Must be a nightmare to maintain.
- indeed30 3y agoIt makes enterprise sales easier, since it removes a common objection from security, privacy and compliance teams.
- Cthulhu_ 3y agoWouldn't having separate databases (with separate users (per organization)) make more sense from a security point of view? I have no knowledge of these things, I've never actually worked with more than one database in a mysql instance. edit: I tell a lie, I separated the forums and wordpress databases on a website I run.
- vincnetas 3y agoOne DB server can have multiple DB's. In this case we are talking about single DB (not server) containing multi thousands of tables. And im curious what is the use case for such designs.
- jiggawatts 3y agoSAP with a bunch of plugins plus custom tables added for various purposes. This is for managing the finances of 200K staff across 2,500 locations.
- stef25 3y agoIsn't that just bad design on the part of SAP ?
- miroljub 3y agoNon necessarily. Would it be "better" if they had one table with json/xml/whatever and handled schema in code? They made a trade-off they found right. When they hit the limit with their approach, they even implemented their own DB (S4/Hana) to support their system.
- tambourine_man 3y agoI was surprised as well, I feel one may need a DB to sort that DB. A meta DB :)
- pravus 3y agoI worked at an educational institution where we ran an academic-focused Enterprise Resource Planning (ERP) system that was fairly large. Not quite 40k tables, but it had over 4k. To give you an idea of how this was organized: * Most simple things like a "Person" were multiple tables because you had to include audits and historical changes for each field * A "Person" wasn't even all that useful because it included guests or other fairly transient entities like vendor contacts so you had an explosion of more tables as you classified roles into "Student", "Faculty", "Employee", etc... (many with histories as above). * Addresses and other non-core demographic information were usually sharded into all sorts of categories like "primary", "parent's", "last known good", "good for mailing", etc... (more histories, etc...) * All coded information like label types such as "STUDENT", or "MAILING" were always handled as separate validation tables with strict FK constraints and usually included extra meta information like descriptions and usage notes within parts of the system. * Each functional sub-system (HR, Payroll, AR, AP, etc.) had its own dedicated schema. * All external jobs, processes, and external integrations were configured separately. * All enterprise integrations usually had a whole a dedicated schema for configuration. * Most parts of the interactive web UI were database driven (Oracle's Apache mod PL/SQL) with many templates and other components stored in large collections of tables. I'll stop there, but basically just imagine a very large application that tries to be 100% database-driven. That's how you get a lot of tables.
- Spivak 3y agoAnd honestly, I kinda get it. Until you run into a case where your volume is such that you physically can't run on the db then run it on the db. I run all my job processing off the DB and couldn't be happier. I have to hit "can't run along side the real data" and "can't run in its own db" before I'll need to consider something else. It probably feels weird for devs to drive the UI off the db but it's just Wordpress by another name.
- Cthulhu_ 3y agoI've worked with / on an application like that, it had all form fields awkwardly configured in a database, plus a complicated database migration script to add, remove and update those fields. When I rewrote the application I just hardcoded the form fields, nobody should need to do a database migration to change an otherwise mostly static form.
- marcosdumay 3y agoAny ERP will bring you near that.
- lenkite 3y agoSaw ~60k tables in one famous ecommerce company backend primarily due to sharding - spread across multiple DB's of-course.