October 28, 2003 at 12:19 pm
I am working on a job where different applications are setup as different databases on a single SQL Server. Each application references an Employees and Departments tables. Naturally we don’t want to have to maintain these identical tables in multiple databases. So, we are looking at setting up a view in each database that will reference just one ‘master’ table for both the Employees and Departments tables. I have done this for one application and it has worked ok. However, now that I am referencing Employees and Departments as views instead of tables I see no way to setup relationships between the Employees or Departments and the other related tables. Or, is there another way to setup the databases, so there is only one ‘master’ table for each Employees and Departments.
Thanks,
Brian
October 31, 2003 at 8:00 am
This was removed by the editor as SPAM
October 31, 2003 at 2:27 pm
Define the relationships in the db that holds the tables. When you use the views (which is a good solution) the underlying tables enforce the relationships, constraints, etc.
Andy
November 2, 2003 at 5:14 pm
Why do you want to use extra views?
Why not use the MainDB.Owner.TableName from like that:
select EMP.emp_name,
SAL.amount
from OneOwner.sales as SAL join MainDB.SecondOwner.Employees EMP on SAL.emp_code = EMP.emp_code
where EMP.emp_code = 10
at least so it's clear for everybody that you are accessing the data in the Main database.
The performance (execution plan) is the same as it would be a single DB access.
As performance hint : you should use the same owner inside the same query otherwise the SQLServer has to do some extra work to check all the permissions.
Bye
Gabor
Bye
Gabor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply