I was going over AdventureWorks2008 database and wanted to create a new table that associates a product to a sales person.
There is a many-to-many relationship between those tables.
The question is,
Of two schemas, Sales and Production, does ProductSalesPerson table belong to?
ProductSalesPerson doesn't neccessarily belong to either schema.
Should I create a new schema for this associative table?
On
In real life there would be a user to ask. But if we have to guess, it is normal for sales people to be assigned products as their beat, and the responsibility for that decision is taken by Sales rather than Product Development. So I would say that your intersection should belong in SALES rather than PRODUCTION.
Why are Production and Sales are in different schemas?
On the assumption that they just are, and you can't change that -- put it in whatever schema is responsible for the bigger perspective share. Meaning, if your question is usually "who is responsible for selling product x?" -- it should go into Production. If it's more of the "what product does salesperson x sell?" -- it should go into Sales.
This is kind of a screwy design imho, for the very reason of cross-schema relationships not having an obvious home (but if there are good reasons for setting things up this way, I would appreciate being enlightened).