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

[yutlin]

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!

 

[Catadmin]

IMHO, Databases, like tables, should contain similar information. If you're building a completely new program that has nothing to do with the programs before it, I would recommend creating a new database.

Of course, I know there are RL situations where people have split databases up into separate ones. Sometimes it is for Data Warehousing purposes. The company I work for does software development for the web. They offer several different programs and I know that 1 program has at least 6 or 7 different databases.

I've only been working with them a couple of weeks, so I don't know the logic that went into creating these various DBs. But I suspect it was a not-quite haphazard endeavor to deal with the sudden influx of business they had and having only a base understanding of how SQL works.

I'd love to hear if anyone has a multi-database single program setup and what the business needs for setting it up that way were for.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[yutlin]

Catadmin,

That sounds like it may be the exact opposite end of the spectrum, creating multiple databases for a single app as aposed to creating a single database for multiple apps. I too am curious if there is an accepted standard or best practice regarding this subject. To me it makes sense to group related tables into databases but I'm not sure if I'd then run into a significant performance hit when I needed to link an Employee table in the Staff database to a Work table in the Job database for instance.

Does anyone else have additional information on the topic?

 

[Catadmin]

Just a note. Remember performance has almost as much to do with your hard drive (available space, file/filegroup setup, other programs running) as how you set up your database itself.

I would definately recommend looking into the option of splitting up your database if it's one DB for multiple programs. Also, look into changing the way you use files and file groups. This will probably be a long process, but you should at least do the research so you'll have a better handle on how things are working right now.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[mrdenny]

The best practice for this sort of thing is what the system and enviroment requires.

I've seen it done both ways, and it made sense in each case.

It's important not to get locked into one way of thinking.

There is no performance hit of doing a join between databases on a server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Denny,

Can you give me an example of how and why the multiple dbs were used? Since this is my first time encountering it, I'd like to know for future reference.

I hate ignoring solutions just because I don't know anything about them. @=)

Thanks!



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[mrdenny]

When you need to ensure that specifc users can't get access to the data.
If you want to be able to break the system up accross more than one SQL Server.

Those are pretty much the big reasons.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)