Database Object Placement for Custom Add-on Software

Question

What is everyone's recommendations on where to place database objects (tables, procs, views etc.) for a custom add-on to another application's database?

Should they be kept separated like so:

ErpInventoryAccountingWare.dbo
CustomIntegratedPortal.dbo

Or, could the add-ons objects be placed in the same database. The objects would be named appropriately, and would assume the same permissions.

Keeping the objects on the same server but in different databases looks like a clean solution to me, as long as you're prepared to alter your scripts to accommodate the other databases name.

I'm using SQL 2000, SQL 2005.

Answer 1

If you put them in a different database, a backup of just the application's database will miss them, so it presents a gotcha for operational maintenance of the application.

On SQL Server 2005, making your own schema will help prevent namespace pollution should the application's developer decide to add a table with a name that clashes with one of yours.

On SQL Server 2000, making the tables owned by a specific application user will give you some of the benefits of a schema, while allowing them to coexist in the same database.

Answer 2

Depends on the vendor; some vendors don't play nice with foreign things in their database; I've seen vendor code that removes anything that is not theirs. Things can disappear during upgrades too.

But if it's a pretty stable well-behaved application, keeping everything together is an advantage in my mind. Just prefix everything, e.g. ABC_MyProc, ABC_MyTable, so they can be identified.