Relating views to tables

  • 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

  • This was removed by the editor as SPAM

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • 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