Database Design Question

Which of the following options is best?

Option A)

One database with all the tables for all applications (HR, TechStaff, Inventory, Purchasing, etc.). The bonus to this is all the data is stored in one place and the data is not duplicated, but when the database has to come down, all applications will be unavailable. Plus, volume may be present an read/write row/column issue.

Option B)

Multiple databases... one for each application. Then to replicate the necessary tables to each database as needed. For example, replicate the employee table (ID, firstname, lastname) to the databases that would require that data. The replication would be setup to be one-way replication. Bonus is that if one database goes down, the others are still available, but a downside is the same data is stored in multiple places and updated through replication (which could slow down the server).

Thoughts?

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.

Thanks

|||

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

|||

Sunil Agarwal wrote:

Seems like you are using 'database' to refer to an 'instance' of SQL Server? If your primary concern is high-availability, you may want to consider cluster or mirroring or even log shipping.

Thanks

Seperate databases vs a single database on one instance os SQL Server.|||

Cristian Lefter wrote:

I would go with B option. It proved to be more flexiblein my case. I used a database for each application. The employees were stored in a separate database that was populated from Active Directory. Each application had an employee table that contained only the authorized employees for that specific application.

That is what I was doing now... but instead of maintaining multiple employee tables across many databases, I would setup the server to automatically replicate the employee tables from the main employee databases to cascade the changes down to all the other databases the required some sort of employee data.|||Both have their benefits but replication does seem to be a bit of an overhead in this case.

There is an alternative and that is to create individual databases for each application. Then, rather than replicate the data, you can create a view in the source database selecting the data you need to display in new database (eg EmployeeList - a list of employees in a HR database). This can join several table together as you would in any normal view. In the new database you create a new view which contains a simple SQL statement of "SELECT * FROM HrDbName.EmployeeList" and call it anything you like. You can then use this view as you would any other table or view. Provided your users have access to the data in both databases then it will appear as though the data resides in the same database.

This does not stop the problem of one database being unavailable affecting the others but it should result in a number of smaller databases targeted at one objective while still enabling you to effectively share the data between applications.

If the databases are on a different server then you can include the server name in the SELECT statement but you will also have to set up security between the servers.

This is a technique I have used for externally provided applications where we write our own queries. By setting up an enquiry database with views direct to the tables in the suppliers database I can create as many views as i need without any dabger of breaking the application now or in the future.