18

So I've been working on a project where I have lots of data. In some collections of data, there are parts of data that link to other collections of data as in relational databases.

However, I am confused over whether I should use multiple databases or multiple tables. I've tried to find out, and answers from Quora helped - from what I understand, if I have collections of data that link together, I should use one database containing multiple tables (these tables would link together).

If my understanding is correct, then how far is this practice appropriate? For example, would it still be okay if you had over 20 tables in one database? Would you use one

Also, when would you use different databases if you can put everything in one database containing multiple tables?

I hope this question isn't too vague or anything, but I could not find clear answers that explain the purposes of each and therefore why you would use multiple databases over multiple tables or vice versa.

Farbod Salamat-Zadeh
  • 19,687
  • 20
  • 75
  • 125
  • As a simple rule of thumb: one database per application. Different databases for different applications. – Sergio Tulentsev Jun 20 '16 at 09:47
  • @SergioTulentsev Multiple apps on a shared database is very common - duplicating data among different apps is just a maintenance nightmare. – reaanb Jun 20 '16 at 09:50
  • @reaanb: note the "simple" part. If one _needs_ to run several apps on the same database, he won't ask this question. The question is, "should my _one_ app use several databases?" – Sergio Tulentsev Jun 20 '16 at 09:52
  • 1
    @reaanb: thinking about it now, I should have removed the last sentence. What I really meant is "one database per app, or less. Certainly not multiple. Unless you really know what you're doing" – Sergio Tulentsev Jun 20 '16 at 09:58
  • @SergioTulentsev Is this because of performance or because it would be easier to use (or both)? – Farbod Salamat-Zadeh Jun 20 '16 at 10:15
  • @FarbodSalamat-Zadeh: logical grouping, mostly. Then some performance benefits too, maybe (depends on the DBMS in question) – Sergio Tulentsev Jun 20 '16 at 10:16
  • @SergioTulentsev Thanks. And are there any examples of situations where you would use multiple databases over multiple tables (or a few databases each containing multiple tables)? – Farbod Salamat-Zadeh Jun 20 '16 at 10:21
  • 2
    Note that 20 tables in one database is nothing - hundreds of tables are not unusual. Multiple database apps are usually apps which have their own data requirements but also have to integrate with another existing app or service's database. – reaanb Jun 20 '16 at 10:25
  • @FarbodSalamat-Zadeh: there certainly are some examples. Mainly, when circumstances dictate it. Off the top of my head, maybe you inherited a legacy app with a legacy database. You don't want to modify that piece of... code and data. So you just build your new stuff at the side, in another database, and old queries keep pointing to the old database, until you're done with migration, or something. – Sergio Tulentsev Jun 20 '16 at 10:25
  • Thanks @reaanb - I was thinking that 20 was far too many, but I guess not! You can post your suggestion as an answer if you like. – Farbod Salamat-Zadeh Jun 20 '16 at 10:29
  • Thank you @SergioTulentsev for the clarification. You could post your suggestion as an answer too if you want. – Farbod Salamat-Zadeh Jun 20 '16 at 10:30

1 Answers1

9

20 tables in one database is nothing - hundreds of tables are not unusual. Try to keep logically related information together and unrelated information separate. Try to avoid multiple databases or tables with the same design or purpose.

Multiple database apps are usually apps which have their own data requirements but also have to integrate with another existing app or service's database.

reaanb
  • 9,806
  • 2
  • 23
  • 37