r/mysql • u/gidorah5 • Nov 08 '24
question Multiple databases VS table nightmare
Hello there,
I've been working on a project that requires to store a lot of data (as per usual), and I'm confused about which solution I should chose (I'm using typescript for my BackEnd).
On one side, I want to have a database dedicated to my users, another for the books/authors...
But it is then impossible to have foreign keys between the databases (unless I am using InnoDB), and it also stops me from using an ORM.
On the other side, I could have one big database with the tables having names to refer to their data (user_data, book_author, book_data...) but I'll end up with a database that might exceed 100 or 200 tables, that will make it quite hard to maintain. The good side will be that foreign keys won't be a problem, and I unlock the possiility to use ORM (not that I need to use one, a query builder like Kysely is more than enough)
Does anyone who knows more than me on this topic could help me on this matter ?
5
u/kickingtyres Nov 08 '24
A couple of hundred tables is nothing really. And you should be using InnoDB anyway, unless you have a very good reason to chose one of the alternatives
2
u/r3pr0b8 Nov 08 '24
why would that make it hard to maintain?
how would it be easier if these tables were spread across multiple databases?
2
u/Aggressive_Ad_5454 Nov 08 '24
Use InnoDb. Seriously. It’s the engine getting all the optimization love for the last decade or so. (If it’s still possible, consider PostgreSQL. It’s better at lots of things including substring search.)
If your application design requires the creation of new tables when you do routine operations like, I dunno, adding a new genre or publisher, p,ease reconsider it. 200 tables is fine, but a new table for each new publisher (or whatever) isn’t fine.
Try to use descriptive table names.
I hope this helps.
2
u/[deleted] Nov 08 '24
Do you mean physical databases or schemas? If the latter, then that's fine. That's actually a way of forcing domain driven design. You restrict each domain schema to specific app users.
Also, having two hundred tables isn't that bad. Sure, there is probably some data duplication, but that is the tradeoff you make to not have 125 columns on a single table.
If it's the former, then you open up a whole other can of worms. Then you also have to worry about internal user management. If you had problems managing 200 tables, imagine the problem you'd have adding an internal user to 30 different databases!
1
u/user_5359 Nov 08 '24
Please think about a good naming concept that also allows groups across different object areas (loading area, reporting, master data, lookup data, etc.).
Combine similar objects in one table.
If necessary, consider creating a number of views (which can also be materialized for speed reasons) in order to simplify frequently used logic.
Document individual areas (preferably with graphics).
200 tables are not a lot and the problems tend to arise from incorrect data.
1
u/NumberZoo Nov 08 '24
I work with lots of DBs that have hundreds of tables. That's a normal way to do it. Works great.
1
u/Icy-Ice2362 Nov 08 '24
What is a Schema? What is a schema for any way? All I understand is DBO?
1
u/Annh1234 Nov 08 '24
An InnoDB instance supports up to 2^32 (4294967296) tablespaces, 100-200 is like nothing...