Relationships

[CraigBFG]

Is it possible/good practice to have relationships from 2 seperate fields within one table to a single field within another?

Eg
TableUsers
UserId / UserName / User Type
data...
1 / TheCompanyLtd / Developer
2 / TheOtherLtd / Client
3 / Company215Ltd / Client

TableSites
SiteId / SiteName / DeveloperId / ClientId
data...
1 / Site1 / 1 / 2
1 / Site2 / 1 / 2

I don't want to maintain multiple tables of different users if I can help it - redundancy and all that...Hope this makes sense.

Regards
CraigBFG

 

[CraigBFG]

So, even though all users are maintained in a single table, the PK can be referenced to twice in another?

Then in relationships, you have to enter the same table twice :

t_Users --- T_Sites ---t_Users_1

A developer is responsible for a site.
A client owns the site.
Both developer & client are users of different types.

My test SQL is :

SELECT [tblSites].[SiteName], [tblUsers].[OrgName], tblUsers_1!Orgname AS DevOrg
FROM tblUsers AS tblUsers_1 INNER JOIN (tblUsers INNER JOIN tblSites ON [tblUsers].[UId]=[tblSites].[UId]) ON tblUsers_1.UId=[tblSites].[DevId];