Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

How to create a view Table with multiple foreign keys from a single table.

How to create a "ContactInformation" view table in order to see all data fields from the associated foreign key table, mainly in question 3 phone numbers FK_HomePhoneID, FK_WorkPhoneID, and FK_MobilePhoneID, these foreign keys are associated with the phone table PK_PhoneID.

the view table must contain
AccountID
ProfileID
HomePhoneID: Number, isPrimary, PhoneTypeCode,
WorkPhoneID: Number, Ext, isPrimary, PhoneTypeCode
MobilePhoneID: Number, isPrimary, PhoneTypeCode




See attachment for details
Note: Design partly taken from larger system.


create table Account (AccountID uniqueidentifier default newsequentialid() not null, AccountNumber varchar(128) null unique, UserID varchar(128) not null, Password nvarchar(128) not null, RegistrationDate datetime default getdate() not null, constraint PK_Account primary key (AccountID));
create table PhoneType (PhoneTypeID uniqueidentifier default newsequentialid() not null, PhoneTypeCode int not null unique, Name nvarchar(50) not null, constraint PK_PhoneType primary key (PhoneTypeID));
create table Phone (PhoneID uniqueidentifier default newsequentialid() not null, Number nvarchar(50) not null, Ext nvarchar(10) null, isPrimary bit default 0 not null, PhoneTypeID uniqueidentifier not null, constraint PK_Phone primary key (PhoneID));
create table AccountContactInformation (AccountContactInformationID uniqueidentifier default newsequentialid() not null, ProfileID uniqueidentifier not null unique, ContactInformationID uniqueidentifier not null unique, constraint PK_AccountContactInformation primary key (AccountContactInformationID));
create table CountryOfCitizenship (CountryOfCitizenshipCode varchar(2) not null, Name nvarchar(100) not null unique, constraint PK_CountryOfCitizenship primary key (CountryOfCitizenshipCode));
create table ContactInformation (ContactInformationID uniqueidentifier default newsequentialid() not null, FirstName nvarchar(100) not null, LastName nvarchar(100) not null, AddressID uniqueidentifier not null, CountryOfCitizenshipCode varchar(2) not null, AccountCurrencyID uniqueidentifier not null, HomePhoneID uniqueidentifier null, WorkPhoneID uniqueidentifier null, MobilePhoneID uniqueidentifier null, constraint PK_ContactInformation primary key (ContactInformationID));
create table Profile (ProfileID uniqueidentifier default newsequentialid() not null, AccountID uniqueidentifier not null unique, constraint PK_Profile primary key (ProfileID));
create table AccountPhone (AccountPhoneID uniqueidentifier default newsequentialid() not null, ProfileID uniqueidentifier not null, PhoneID uniqueidentifier not null unique, constraint PK_AccountPhone primary key (AccountPhoneID));
alter table Phone add constraint FK_Phone_PhoneTypeID_PhoneType foreign key (PhoneTypeID) references PhoneType (PhoneTypeID);
alter table ContactInformation add constraint FK_ContactInformation_HomePhoneID_Phone foreign key (HomePhoneID) references Phone (PhoneID);
alter table ContactInformation add constraint FK_ContactInformation_WorkPhoneID_Phone foreign key (WorkPhoneID) references Phone (PhoneID);
alter table ContactInformation add constraint FK_ContactInformation_MobilePhoneID_Phone foreign key (MobilePhoneID) references Phone (PhoneID);
alter table AccountContactInformation add constraint FK_AccountContactInformation_ContactInformationID_ContactInformation foreign key (ContactInformationID) references ContactInformation (ContactInformationID);
alter table ContactInformation add constraint FK_ContactInformation_CountryOfCitizenshipCode_CountryOfCitizenship foreign key (CountryOfCitizenshipCode) references CountryOfCitizenship (CountryOfCitizenshipCode);
alter table Profile add constraint FK_Profile_AccountID_Account foreign key (AccountID) references Account (AccountID);
alter table AccountContactInformation add constraint FK_AccountContactInformation_ProfileID_Profile foreign key (ProfileID) references Profile (ProfileID);
alter table AccountPhone add constraint FK_AccountPhone_PhoneID_Phone foreign key (PhoneID) references Phone (PhoneID);
alter table AccountPhone add constraint FK_AccountPhone_ProfileID_Profile foreign key (ProfileID) references Profile (ProfileID);

Open in new window

cphone.jpg
0
robertkc
Asked:
robertkc
  • 11
  • 6
  • 3
2 Solutions
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
When there are more than 1 FK in a table that would just indicate that you would join the table multiple times on a different key each time.

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The only other thing you would need to do is to decide if you wanted the values to come back NULL for the phoneIds that do not match.

CREATE VIEW dbo.viewName
AS
SELECT A.AccountID, P.ProfileID,
HP.Number as HomePhone, HP.IsPrimary as HPIsPrimary, HP.PhoneTypeCode as HomePhoneTypeCode,
WP.Number as WorkPhone, WP.Ext as WorkExtension, WP.IsPrimary as WPIsPrimary, WP.PhoneTypeCode as WorkPhoneTypeCode,
MP.Number as MobilePhone, MP.IsPrimary as MPIsPrimary, MP.PhoneTypeCode as MobilePhoneTypeCode
FROM dbo.Account A
INNER JOIN dbo.Profile P ON A.AccountID = P.AccountID
INNER JOIN dbo.ContactInformation CI ON P.ProfileID = CI.ContactInformationID
LEFT JOIN dbo.Phone HP ON CI.HomePhoneID = HP.PhoneID
LEFT JOIN dbo.Phone WP ON CI.WorkPhoneID = WP.PhoneID
LEFT JOIN dbo.Phone MP ON CI.MobilePhoneID = MP.PhoneID

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Hi Robert,

Think I know someone you work with.

That diagram is quite involved... Can only see one use really for Phone Type and that is for Account Phone. Which I also have read as being mandatory (ie inner join).

The Home, Work, and Mobile could also have extension etc and an indicator as to preferred / primary phone - can do more on that if you want... dbaduck has them included above...

I think all the data relationships are there matching your diagram - not every column, but that is easy to add in.



SELECT      dbo.Account.AccountID, dbo.Profile.ProfileID,
            dbo.ContactInformation.HomePhoneID, isnull(HP.Number,'') as HomePhoneNo,
            dbo.ContactInformation.WorkPhoneID, isnull(WP.Number,'') as WorkPhoneNo,
            dbo.ContactInformation.MobilePhoneID, isnull(MP.Number,'') as MobilePhoneNo,
            dbo.AccountPhone.PhoneID, isnull(AP.Number,'') as AccountPhoneNo,
            dbo.PhoneType.Name as AccountPhoneType
 
FROM        dbo.ContactInformation 
INNER JOIN  dbo.AccountContactInformation ON dbo.ContactInformation.ContactInformationID = dbo.AccountContactInformation.ContactInformationID 
INNER JOIN  dbo.CountryOfCitizenship ON dbo.ContactInformation.CountryOfCitizenshipCode = dbo.CountryOfCitizenship.CountryOfCitizenshipCode 
INNER JOIN  dbo.Profile ON dbo.AccountContactInformation.ProfileID = dbo.Profile.ProfileID 
INNER JOIN  dbo.Account ON dbo.Profile.AccountID = dbo.Account.AccountID
INNER JOIN  dbo.AccountPhone on dbo.Profile.ProfileID = dbo.AccountPhone.ProfileID    -- if account phone, why profile id ?
INNER JOIN  dbo.Phone AP ON dbo.AccountPhone.PhoneID = AP.PhoneID 
INNER JOIN  dbo.PhoneType ON dbo.Phone.PhoneTypeID = dbo.PhoneType.PhoneTypeID        -- PhoneType does seem superfluous
LEFT JOIN   dbo.Phone HP ON dbo.ContactInformation.HomePhoneID = HP.PhoneID 
LEFT JOIN   dbo.Phone WP ON dbo.ContactInformation.WorkPhoneID = WP.PhoneID 
LEFT JOIN   dbo.Phone MP ON dbo.ContactInformation.MobilePhoneID = MP.PhoneID 

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mark WillsTopic AdvisorCommented:
Apologies did not add in the create view.... and the extra columns - some of which do not make a lot os sense in every day use - mobile extension numbers ?

And would prefer to replace thos IsPrimary columns to a single column as shown below as "PreferredContact"...


Create View ContactInformation
as
SELECT      dbo.Account.AccountID, dbo.Profile.ProfileID,
            dbo.ContactInformation.HomePhoneID, isnull(HP.Number,'') as HomePhoneNo, HP.extension as HomeExt, HP.isprimary as HomeIsPrimary,
            dbo.ContactInformation.WorkPhoneID, isnull(WP.Number,'') as WorkPhoneNo, WP.extension as WorkExt, WP.isprimary as WorkIsPrimary,
            dbo.ContactInformation.MobilePhoneID, isnull(MP.Number,'') as MobilePhoneNo, MP.extension as MobileExt, MP.isprimary as MobileIsPrimary,
            dbo.AccountPhone.PhoneID, isnull(AP.Number,'') as AccountPhoneNo, 
            dbo.PhoneType.Name as AccountPhoneType, AP.extension as AccountPhoneExt, AP.isprimary as AccountPhoneIsPrimary,
            case when HP.isprimary = 'true' then 'Home'
                 when WP.isprimary = 'true' then 'Work'
                 when MP.isprimary = 'true' then 'Mobile'
                 ELSE 'Account'  end as PreferredContact
 
FROM        dbo.ContactInformation 
INNER JOIN  dbo.AccountContactInformation ON dbo.ContactInformation.ContactInformationID = dbo.AccountContactInformation.ContactInformationID 
INNER JOIN  dbo.CountryOfCitizenship ON dbo.ContactInformation.CountryOfCitizenshipCode = dbo.CountryOfCitizenship.CountryOfCitizenshipCode 
INNER JOIN  dbo.Profile ON dbo.AccountContactInformation.ProfileID = dbo.Profile.ProfileID 
INNER JOIN  dbo.Account ON dbo.Profile.AccountID = dbo.Account.AccountID
INNER JOIN  dbo.AccountPhone on dbo.Profile.ProfileID = dbo.AccountPhone.ProfileID    -- if account phone, why profile id ?
INNER JOIN  dbo.Phone AP ON dbo.AccountPhone.PhoneID = AP.PhoneID 
INNER JOIN  dbo.PhoneType ON dbo.Phone.PhoneTypeID = dbo.PhoneType.PhoneTypeID        -- PhoneType does seem superfluous
LEFT JOIN   dbo.Phone HP ON dbo.ContactInformation.HomePhoneID = HP.PhoneID 
LEFT JOIN   dbo.Phone WP ON dbo.ContactInformation.WorkPhoneID = WP.PhoneID 
LEFT JOIN   dbo.Phone MP ON dbo.ContactInformation.MobilePhoneID = MP.PhoneID 

Open in new window

0
 
robertkcAuthor Commented:
Thanks mark_wills and dbaduck

@Mark
"Think I know someone you work with."
Thats always nice to know :) australia is quite small - its possible yes.

In regards to the profile table, this was a table that I never really liked to begin with, it makes things rather complicated for the development team.
(also slows down performance, extra query thats not necessary)

It was created to satisfy a business requirements, I still think it should be removed.

You are right, AccountPhone should really be called "ProfilePhone" -- the profile table was created after the account table and was not renamed, it be fixed.
Yes PhoneType could very well be removed.
0
 
robertkcAuthor Commented:
@mark what is "AccountPhone.PhoneID"?

a user account can add a number of
-home numbers,
-work numbers, and
-mobile numbers

however there can only be
1 primary home number, (ie set isPrimary = true)
1 primary work number
1 primary mobile number


0
 
Mark WillsTopic AdvisorCommented:
I see, I thought that Primary referred to the preferred contact... Sorry about that. That preferredcontact column is a total waste of effort...

AccountPhone.PhoneID is held in accountphone hanging off the profile table (toward top right)

That profile table is interesting - it's most compelling feature is it's name is much shorter that accountcontactinformation, and either profile needs contactinformationid and remove that really long named table. or, accountcontactinformation needs accountid and kill profile table.

Then you could possibly kill accountPhone as well - is it possible to have accounts with no contacts ? Even a corporate entiry will have a contact if not a physical contact then atleast a logical contact - well that's one approach I guess.

What is profile used for ? There is a business requirement ? It looks like for no other reason than to support an accountphone.
0
 
robertkcAuthor Commented:
That was actually my fault for not being clear.

The master copy is much larger -- the profile table link up all user details to 1 profileid.

A registered user can have a single account, system creates accountid, etc
user can then have a profile, system creates profileid.

User might in somecases relocate to another country, maybe for a temporary/holiday or permanent.

A new profile can be created and then linked to the user's existing account ie the user can keep the same "account" without re-registering, only his profileid changes. (some minor changes to the database design required).

User can switch back and forward between "profiles" when his overseas and then returns back home (different countries has different restrictions, different features, etc)

Profile is like having multi-profile account, where the login credentials/login history, referee, transaction account (funds) etc..sames the same across all linked profiles.

If the profile table was removed, how can the user change profile now? a new account must be created? but why when its the same person!

"1 person 1 account, many profiles for each country where relocating." thats the main purpose, should there be a reason to remove the profile table? it does make things complicated, but it seems neccessary?

0
 
robertkcAuthor Commented:
Note: user can have approximately 103 profiles, 1 account per country, 1 account per user.

At this time, i cant think how profile can be removed?
0
 
robertkcAuthor Commented:
Correction "user can have approximately 103 profiles, 1 profile per country, 1 account per user"
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
If it were me, I would eliminate the Profile table and use the AccountContactInformation table (maybe with a new name) that would be your mapping table.  Right now you have 2 mapping tables and I think that they could be consolidated and use a single Key to do the mapping to the other tables.

0
 
robertkcAuthor Commented:
@dbadluck

The follow are some tables not seen in the diagram above

Account has
1.1 AccountSecurityQuestions
1.* AccountLoginHistory
1.* AccountBankAccount
1.* AccountCreditCard
etc

Profile has
1.1 AccountStatus <--- this data should really be in the "Profile" table.
1.1 AccountContactInformation
1.1 AccountBusinessInformation
1.* AccountNotifications
1.* AccountAddress
1. * AccountPhone
1.* AccountSecurity
etc

0
 
robertkcAuthor Commented:
"If it were me, I would eliminate the Profile table and use the AccountContactInformation table (maybe with a new name) that would be your mapping table.  Right now you have 2 mapping tables and I think that they could be consolidated and use a single Key to do the mapping to the other table"

This could actually work! Just trying to figure out how
0
 
Mark WillsTopic AdvisorCommented:
Not entirely neccessary to remove, and I might still be missing a fundamental point as to what benefit a profile gives above and beyond an account - maybe regional settings, but it is not evidenced from what I can see. Maybe some of those restrictions, features, banking details are not part of the current image.

The account is still active all this while, so, maybe they are a bit too closely aligned, hence some of the confusion. Maybe the profile should represent current work / location and is nothing more than a set of extended attributes to reflect current circumstance, but would not be hanging too much off that - it would be easy to become trapped into multiple / duplicated data everywhere and then trying to sift through all that down the track will be "fun".

Might be akin to wanting two different "profiles' within EE, I do know that there are a few experts that would love to be able to do that.

Maybe a moot point, because it is a business requirement.
0
 
robertkcAuthor Commented:
I try my best to explain the business requires without giving away too much.

"Maybe the profile should represent current work / location"
I guess you can think of profile as a new user profile per country.

The system is a platform to manage other applications and its users.
In regards to restrictions and features
-Account fees vary between countries
-bank accounts, credit cards, and address details: only the country where the user profile country is enabled the country item can be attached. (ie if your profile is an Australian profile you can only attach a Australian bank account, credit card and Australian address etc UNTIL you register a new profile for another country).
-Features are provided 3rd party/connected application services and certain access to these 3rd party services may not be available in all countries or restricted in some countries.

Profile => allows registration for 1 CountryRegion , country restriction for connected services
Profile => has new contact/address details
Profile => has new financial banking details

Account => has everything else that can be shared between the CountryRegion/profiles , mainly involving user credentials, login history, securityquestions, owners name, profilepermissions/status etc

There are currently some minor changes to the design that will need to be made (not isnt the best design), as for keeping the profile table or removing profile table, it has not be decided yet.

0
 
Mark WillsTopic AdvisorCommented:
Thought that the Account would have the banking / financial details. Can understand restriction depending on locations - that does make sense, and yes keep profiles quite seperate in that regard, and likely to keep it. But then what is contactinformation - is that account specific or profile specific ? If profile specific, then is there a need for that really long name mapping table considering it is unlikely that any of that information can be shared ? And then should things like country of citizenship be account specific ?

Anyway, you have closed the question now, and while I am sure we could discuss the various merits and designs, if you are still tuning the design, then it is possibly a little premature, and always difficult to discuss if you cannot disclose too much.

Thanks for the question, the points, and always enjoy dbaduck's involvement (it has been a little while since last time)
0
 
robertkcAuthor Commented:
Closed the thread as it was was going off topic.

A new thread will be open to address the necessary questions in hand if any.

In regards to country of citizenship, you are right, this should really be under "account" and should be shared as suggested.
i,e Citizenship is not variable across all profiles (can not be different from one profile to the next etc).

0
 
robertkcAuthor Commented:
The following tables are associated with the user's account and profile details

<<Account>>,

1.1 AccountStatus
1.1 AccountAccountType
1.1 AccountSecurityQuestion
1.* AccountNotification
___ AccountCountryRegion <--- remove?
1.1 AccountCountryOfCitizenship
1.* AccountCurrency
1.* AccountLoginHistory
1.* AccountReferee

<<Profile>>
1.1 AccountContactInformation
1.1 AccountBusinessInformation
1.1 AccountTimezone
1.* AccountEmail  
1.* AccountAddress
1.* AccountPhone
1.* AccountSMSSecurity
1.* AccountBankAccount  
1.* AccountCreditCard  
1.* AccountAutoDeposit  


Good idea to move all tables link/under "Account", then the user can manage all his profile details, and
when the user switches zone/change country the user can selects the profile. The "profile" table then tracks all
preferences/custom to the selected profile, example profilecountry,profileprimarycurrency, etc

(increase performance, meets business requirements, and great for the developer)
0
 
robertkcAuthor Commented:
Post #23698273

If design this way everything belongs to the user "Account" table and all profile perferences are then stored in the profile table.

All problems solved! prefect!
0
 
Mark WillsTopic AdvisorCommented:
Bingo !! Sounds good...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now