I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out whether to divide my state using seperate Databases or using seperate Schema's in the same database. I've done a little reading on SQL Server Schema's and it seems like a natural way to seperate object domains (which I like), but I'm not sure if there may be hidden costs to this pattern.
What are the more practical things I should consider when selecting between these two approachs? If I avoid the
dbo.mytable in favor of
myschema.mytable will I be creating other challenges (or problems) for my architecture?
As a side note... At some point this will be handed over to a real DBA to maintain/support so I'm trying to make sure I don't make their lives harder.