database design question

Hi!

We are designing a big database with multiple parts (like Human Resources, Sales, Production) etc. in Sql server 2005. Should we devide the main parts into separate databases, or should we put all tables in a single database and use schemas to group the parts (like the AdventureWorks sample database?). Is there a general recommendation here?

We are considering multiple databases so one more easily could move one database to a diffrent server if needed.

Hi,

I would suggest you put all the tables in a database and devide them with schemas, just as AdventureWroks shows. Putting the tables across will result in many problems, and performance hits.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

If we devide into multiple databases, we will try to minimize the references between them. After all they represent different services within the enterprise solution.

I realize that the administrative tasks increase as the database count increase, but why would it be related with performance hits? If the traffic to one of the databases becomes an issue, we can easily move it to a new fresh server and thus gain performance.

Could you also be more specific on what other problems you refere to.

Thanks!

Database Design

We have thre projects that are having databases designed for. All of these have a common set of tables (Human Resources, Facilities, States, etc). I would like to build each of these database seperately, but share these common tables among all three pro
jects.
While I know we can join tables between databases, I would like to work with these in the diagram designer. Is there anyway to accomplish this?
You can create views in each DB that reference the common tables in
another DB. I don't think there's any way to show tables from another
database in EM's diagram tool though. Best use a grown-up diagram app
such as MS Visio.
David Portas
SQL Server MVP

Database Design

We have thre projects that are having databases designed for. All of these
have a common set of tables (Human Resources, Facilities, States, etc). I w
ould like to build each of these database seperately, but share these common
tables among all three pro
jects.
While I know we can join tables between databases, I would like to work with
these in the diagram designer. Is there anyway to accomplish this?You can create views in each DB that reference the common tables in
another DB. I don't think there's any way to show tables from another
database in EM's diagram tool though. Best use a grown-up diagram app
such as MS Visio.
David Portas
SQL Server MVP
--

Database Design

We have thre projects that are having databases designed for. All of these have a common set of tables (Human Resources, Facilities, States, etc). I would like to build each of these database seperately, but share these common tables among all three projects.
While I know we can join tables between databases, I would like to work with these in the diagram designer. Is there anyway to accomplish this?You can create views in each DB that reference the common tables in
another DB. I don't think there's any way to show tables from another
database in EM's diagram tool though. Best use a grown-up diagram app
such as MS Visio.
--
David Portas
SQL Server MVP
--