Creating a new DB vs. Creating a new table?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Creating a new DB vs. Creating a new table??

Hey all, Does anyone know of information regarding best practices in MS SQL for when to create a whole new database vs. when to create a new table? I’m working at a new job and I’m used to an enviornment where we created a database for each new database-driven tool. I enjoyed the organization that model provided, however, my new boss is worried about performance problems that could be generated by having multiple databases. He’s especially worried with people using Crystal Reports who would likely just build straight joins between databases. The current situation is one database housing several hundred tables (which to me is a disaster). Any help or suggestions would be appreciated!
If data in different tables belongs together, the tables belong together in a single database. If there is a decent naming convention in place, it shouldn’t really matter how many tables are in a database: I can’t imagine how you would want to keep track of hundreds of databases instead of hundreds of tables … Are you by any chance used to an Oracle environment? My limited exposure to the Oracle "user interface", trying to get someone’s back-up loaded and ready – and failing miserably due to the utterly pointless complexity … well, let’s just say I find SQL Server a lot more result-oriented.
Adriaan, I certainly wouldn’t be a fan of hundreds of DBs either. The thing is, many of the existing tables could be grouped into databases with regards to what application they relate to. For instance: Employee, Employment, Job, Department, Division, etc. tables could all be grouped into an Staff database perhaps. In the meantime I have begun naming my tables with a convention (something that wasn’t done before me) of <appname>__<tablename> however, that sometimes becomes a bit cumbersome and still requires me to wade through a long list of tables in a tool like the SQL Query Analyzer rather than jumping to a database that contains all tables related to the application I am working with. Does anyone have any documentation or links that might relate to this question? Thanks
Yutlin, If you choose an appname_tablename convention, make sure you don’t create redundancy. You might consider creating views to select only appropriate columns, and perhaps also filter rows, for each application.
IMO one table – one db and all tables – one db are two extrems. If i have to choose between them I prefer former. Problem with your naming convention is that some tables may be shared between applications. A few databases containing related tables approach gives you opportunity to distribute dbs on separate servers. Also keeping tables supporting certain application toghether in one db makes deployment and relocation easier.