CODEWITHSUNDEEP
sql-serverdatabase-design
Idan Fanous
Idan Fanous

Reputation: 29

SQL Server : one database with big tables vs many databases

I need to develop project which serve businesses (can reach thousands of businesses). Each business has its own clients, suppliers, documents, vehicles list and more tables.

Each table may have 5k rows and more (can even reach a few million).

Should I have a separate database for each business, or a single database with huge tables which have a business identity column?

Setup 1

db1

table1 - x rows
table2 - x rows
tablen - x rows

db2

table1 - x rows
table2 - x rows
tablen - x rows

dbn

table1 - x rows
table2 - x rows
tablen - x rows

vs

Setup 2

db

table1 - n * x rows
table2 - n * x rows
tablen - n * x rows

Upvotes: 0

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

The database should have one set of tables for all businesses, unless you have an overriding reason for structuring things differently. Here are some reasons:

  • Databases are optimized to have large tables. Performance should be fine using partitions and indexes.
  • Data is structured on data pages. The entire data page is allocated. So a proliferation of small tables means lots of half-empty pages and unused space.
  • Adding a new business is cumbersome.
  • Answering questions such as "how many businesses" is really hard.
  • Maintaining foreign key relationships, data integrity, and indexes is quite challenging.
  • Maintenance -- such as adding a columns -- is needlessly complicated.
  • Backups and restores are more complicated.

There are undoubtedly other reasons as well.

Upvotes: 2

Related Questions