Design - Multiple field relationships between 2 tables

This is a design question but I couldn't find the appropriate newsgroup for
SQL Server Design so I am posting this here.
I have 2 tables Customer and Company. Customer has fields like
AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
WebFilteringCompanyID . All these company ID's are stored in the same Compan
y
lookup table. In most cases (not all), the same CompanyID is used for each o
f
the companyID fields in customers.
One way, of course, is to maintain 4 different lookup tables for
AntiVirusCompany, EmailCompany, SpamFilteringCompany and WebFilteringCompany
and do joins from each of them to their corresponding CompanyID's in the
Customer table but this seems problematic because
1) I need to create 4 new tables
2) Duplication of information across the 4 tables
3) 4 joins while retreiving data.
Is there a better way to implement this scenario?
Thanks,
NaveenIf the CompanyIDs are already stored in the one Company table, why would you
need to create four new tables?
Are you asking how to write the JOIN syntax for this, in which case it's
always best to provide the actual DDL for the tables involved? How about :
SELECT CU.<Field>,
COAV.CompanyName AS AVCompany,
COEM.CompanyName AS EmailCompany,
COSP.CompanyName AS SpamFilteringCompany,
COWF.CompanyName AS WebFilteringCompany
FROM Customer AS CU
JOIN CompanyName AS COAV ON CU.AntiVirusCompanyID = COAV.CompanyID
JOIN CompanyName AS COEM ON CU.EmailCompanyID = COEM.CompanyID
JOIN CompanyName AS COSP ON CU.SpamFilteringCompanyID = COSP.CompanyID
JOIN CompanyName AS COWF ON CU.WebFilteringCompanyID = COWF.CompanyID
HTH
Michael MacGregor
Database Architect
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:35A647D6-4BE5-448C-B313-F058C6AC9C86@.microsoft.com...
> This is a design question but I couldn't find the appropriate newsgroup
for
> SQL Server Design so I am posting this here.
> I have 2 tables Customer and Company. Customer has fields like
> AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
> WebFilteringCompanyID . All these company ID's are stored in the same
Company
> lookup table. In most cases (not all), the same CompanyID is used for each
of
> the companyID fields in customers.
> One way, of course, is to maintain 4 different lookup tables for
> AntiVirusCompany, EmailCompany, SpamFilteringCompany and
WebFilteringCompany
> and do joins from each of them to their corresponding CompanyID's in the
> Customer table but this seems problematic because
> 1) I need to create 4 new tables
> 2) Duplication of information across the 4 tables
> 3) 4 joins while retreiving data.
> Is there a better way to implement this scenario?
> Thanks,
> Naveen|||No, you're doing it right with only one table. When you write a query, join
ti that same table four times, once for each FK column in the Customer Table
,
and alias the results ofeach join as a sdifferent alias, (IN THE QUERY)
as in
Select C.Name Customer,
A.CompanyName AntiVirusCompany,
E.CompanyName EMailCompany,
S.CompanyName SpamCompany,
W.CompanyName WebCompany
From Customer C
Left Join Company A -- For Anti-Virus
On A.CompanyID = C.AntiVirusCompanyID
Left Join Company E -- For eMail
On E.CompanyID = C.EmailCompanyID
Left Join Company Sp-- For Spam
On S.CompanyID = C.SpamFilteringCompanyID
Left Join Company W -- For Web
On W.CompanyID = C.WebFilteringCompanyID
"Naveen" wrote:

> This is a design question but I couldn't find the appropriate newsgroup fo
r
> SQL Server Design so I am posting this here.
> I have 2 tables Customer and Company. Customer has fields like
> AntiVirusCompanyID, EmailCompanyID, SpamFilteringCompanyID and
> WebFilteringCompanyID . All these company ID's are stored in the same Comp
any
> lookup table. In most cases (not all), the same CompanyID is used for each
of
> the companyID fields in customers.
> One way, of course, is to maintain 4 different lookup tables for
> AntiVirusCompany, EmailCompany, SpamFilteringCompany and WebFilteringCompa
ny
> and do joins from each of them to their corresponding CompanyID's in the
> Customer table but this seems problematic because
> 1) I need to create 4 new tables
> 2) Duplication of information across the 4 tables
> 3) 4 joins while retreiving data.
> Is there a better way to implement this scenario?
> Thanks,
> Naveen|||Thanks for the answer. It resolves what I was asking. Thanks to Michael too.
Michael:
My refined question (after reading your answers) was if I should set a
foreign key relationship in the rdbms design stage itself between the 2
tables or if i should do a dynamic join whenever I need to access these
values. Obviously from both your answers it seems a dynamic join makes more
sense. Thanks.
"CBretana" wrote:
> No, you're doing it right with only one table. When you write a query, jo
in
> ti that same table four times, once for each FK column in the Customer Tab
le,
> and alias the results ofeach join as a sdifferent alias, (IN THE QUERY)
> as in
> Select C.Name Customer,
> A.CompanyName AntiVirusCompany,
> E.CompanyName EMailCompany,
> S.CompanyName SpamCompany,
> W.CompanyName WebCompany
> From Customer C
> Left Join Company A -- For Anti-Virus
> On A.CompanyID = C.AntiVirusCompanyID
> Left Join Company E -- For eMail
> On E.CompanyID = C.EmailCompanyID
> Left Join Company Sp-- For Spam
> On S.CompanyID = C.SpamFilteringCompanyID
> Left Join Company W -- For Web
> On W.CompanyID = C.WebFilteringCompanyID
> "Naveen" wrote:
>|||Oh you can set up multiple FKs from one table to another that isn't a
problem, in fact it's a good idea for any developers or DBAs who come after
you, just make sure you name each one appropriately.
Michael MacGregor
Database Architect
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:80CD40AA-5959-4BDD-ABBA-6A0435954288@.microsoft.com...
> Thanks for the answer. It resolves what I was asking. Thanks to Michael
too.
> Michael:
> My refined question (after reading your answers) was if I should set a
> foreign key relationship in the rdbms design stage itself between the 2
> tables or if i should do a dynamic join whenever I need to access these
> values. Obviously from both your answers it seems a dynamic join makes
more
> sense. Thanks.
> "CBretana" wrote:
>
join
Table,
newsgroup for
Company
each of
WebFilteringCompany
the|||Naveen,
Actually, you shopuld do both The DRI constraint should be tehre to validate
data being entered, tio ensure that all the FK values exist, and that no
orphan recrods are created. The dynamicx join is just for the purpose of
EXTRACTING data.
"Naveen" wrote:
> Thanks for the answer. It resolves what I was asking. Thanks to Michael to
o.
> Michael:
> My refined question (after reading your answers) was if I should set a
> foreign key relationship in the rdbms design stage itself between the 2
> tables or if i should do a dynamic join whenever I need to access these
> values. Obviously from both your answers it seems a dynamic join makes mor
e
> sense. Thanks.
> "CBretana" wrote:
>