CODEWITHSUNDEEP
mysqlsqlsql-serverdatabasedatabase-design
paradozx
paradozx

Reputation: 108

Shared data between projects.. create one shared database?

I have three projects and all three projects have certain information which are shared. For instance, lets say a table of vehicles (basic info: year, make, model) and a table of vehicle owners (again basic info: name, address, age). The vehicle and owner information would need to be accessible by the three projects. What are the pros and cons of creating a separate Database for a single access point (2 tables total) versus creating a vehicle and owner table for each of the respective databases for the three projects (6 tables total)? Is there a best practice for this?

Thanks!

Upvotes: 0

Views: 391

Answers (1)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

The best practice is to have all related data in one database. You cannot enforce referential integrity across databases except through complex methods such as triggers. Within a database you can easily use foreign keys to enforce referential integrity.

A best practice since 2005 has been to enforce user/schema separation as well, so you do NOT put everything into [dbo], you have a relevant schema for each set of objects. This classification of objects makes it realistic to have thousands of tables or procedures in a single database.

In a modern database such as SQL 2005 and later there is no need to separate objects across databases. You have tools such as filegroups, partitioning, user/schema separation, etc., that allow you to easily work within one database and it's more efficient.

As an example of why using a single database and segregating objects by schemas is a preferred approach, consider portability of the application. In either the case where you want to move code from development to test to qa then production, or in the case where you want to move code from client to customer, if you have database names embedded in your code this becomes difficult.

Perhaps you have a monthly sales report that is used by accounting to get aggregates. If it is in the same database, the sales report is called as [].[] and you can move from [_dev] to [_prod] and the call is the same. If you are using different databases then the call to the sales report is as [sales].[dbo].[get_monthly_report] (for example). Now, to move to a different set of databases with different names you need to be on a completely separate instance with databases set up exactly the same. In some cases, such as when you're moving code to a client site, this can be very difficult.

Having all the code in one database allows you to NOT use the database name in calls to tables, views, procedures, etc., and your code is much more portable and flexible.

Upvotes: 2

Related Questions