# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star
constellation of multiple facts and conformed dimensions, I'm trying to figu
re out how to separate the physical tables by database. Think about modelin
g the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to s
hared conformed dimensions. The question is - AS A GENERAL RULE, when does
a single database have too many tables? Does each star in the constellation
(for each business process
) merit its own database in the SQL Server instance? If so, how do you set
foreign key relationships across database boundaries, since not having the r
elationships will cause performance to suffer? I have found no way to set f
oreign key relationships ac
ross database boundaries in ERwin.I'm not sure why you would want to have multiple databases. You can already
use filegroups to isolate certain tables or non-clustered indexes across var
ious spindles. Regardless, you cannot have FK constraints that span database
s.

# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star constellation of multiple facts and conformed dimensions, I'm trying to figure out how to separate the physical tables by database. Think about modeling the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to shared conformed dimensions. The question is - AS A GENERAL RULE, when does a single database have too many tables? Does each star in the constellation (for each business process
) merit its own database in the SQL Server instance? If so, how do you set foreign key relationships across database boundaries, since not having the relationships will cause performance to suffer? I have found no way to set foreign key relationships ac
ross database boundaries in ERwin.
I'm not sure why you would want to have multiple databases. You can already use filegroups to isolate certain tables or non-clustered indexes across various spindles. Regardless, you cannot have FK constraints that span databases.