-1

We currently have a single large database for use in reporting. This contains multiple tables, of which only some are related.

For example, we have 5 tables for one division, 2 tables from another division, etc. Data can flow between divisions, but there is not a structural link between the tables - no foreign key relationships, no duplication of tables. We also have some lookup tables that are used by procedures that populate a lot of the tables in each division - calendar, postcodes, that kind of thing.

Is this best as a single large database, or separated out into smaller databases with thematically linked tables? These tables will grow, and the number of them will increase as new divisions are set up. We will notice any degradation over time, or is this so small as to be insignificant?

Any advice or pointers on things to read are gratefully received.

Anthony Genovese
  • 2,067
  • 3
  • 22
  • 34
BishNaboB
  • 131
  • 7

1 Answers1

8

First, define what "large" is. For the rest of your career, you're always going to be working with what you think is big data, and the volumes will keep growing. However, what seems big to you may not seem all that big to others.

Second, ask about backup/restore. Odds are, if you need to do a restore, you're going to want all of the objects restored to the same point in time. The easiest way to do that is to keep 'em in a separate database.

Finally, what's the problem you're trying to solve? Whenever you're thinking about doing something, ask about the problem you're trying to solve. Here, it sounds like you're just randomly looking for ways to use a tool (in this case, breaking data into separate databases.) Focus on the problem at hand, not new solutions. By adding solutions to a problem you don't have, you're only injecting complexity that will come back to bite you later (like at restore time.)

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390