I have two tables: "Applicant_T" and "StreetSuffix_T"
The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:
[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix
The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:
[StreetSuffix_T]
SuffixID <--this is the primary key
Name
For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.
THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?I got the solution from another forum. It is as follows:
|||Having the primary key of one table be associated with mulitple fields in another table is not unusual. However, it does bring a "Spock's raised eyebrow" indicating a table design that could be improved upon. To use the same lookup table more than once you just need to create table aliases so that the query can distinguish between the tables. You also need aliases in the Select clause so you can tell them apart.
create view ApplicantAddresses
( currstreetnumber
, currstreetname
, ...
, currsuffixname
, prevsuffixname
, empsuffixname
)
as
select currstreetnumber
, currstreetname
, ...
, c.name
, p.name
, e.name
from Applicant_T
inner
join StreetSuffix_T c
on currsuffix = c.SuffixID
inner
join StreetSuffix_T p
on prevsuffix = p.SuffixID
inner
join StreetSuffix_T e
on empsuffix = e.SuffixID
SELECT dbo.StreetSuffix_T.Suffix AS CurrSuffix, StreetSuffix_T_1.Suffix AS PrevSuffix, StreetSuffix_T_2.Suffix AS EmpSuffix
FROM dbo.Applicant_T INNER JOIN
dbo.StreetSuffix_T ON dbo.Applicant_T.CurrSuffix = dbo.StreetSuffix_T.SuffixID INNER JOIN
dbo.StreetSuffix_T StreetSuffix_T_1 ON dbo.Applicant_T.PrevSuffix = StreetSuffix_T_1.SuffixID INNER JOIN
dbo.StreetSuffix_T StreetSuffix_T_2 ON dbo.Applicant_T.EmpSuffix = StreetSuffix_T_2.SuffixID
However, the design of Applicant_T seems a bit suspect since you have multiple addresses. Maybe today you want current, former, and employer addresses but in the future you might need another one (spouses employer address, delivery address, second employer address, whatever). With your current design you'd need to add a bunch of new columns whenever you add a new address type.
It may be better to have a separate table that just handles addresses. It might have two keys, one to link back to the applicant_T table and another for the type of address held in that record. (0=current address, 1=previous address, 100=employer address, 1000=dog groomers address).|||McMurdoStation,
You are right. In my pursuit of a solution to a short-term issue, I neglected to consider the longer-term impact of my decision. I am going to change the table design.
Thanks for your input :)|||Yeah, you save less than a byte to normalize that, and lose tons of rotations in the joins.
Sacrifice space for efficiency in runtimes every once in awhile.|||The idea of normalization isn't to save space. If normalizing causes performance bottlenecks you can do things to address that. But that problem is easier to address when and if necessary than having a data model that can't handle change.|||In my particular case, scalability is of high importance. Normalizing the addresses in the manner McMurdoStation suggested, provides such scalability.
