Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ER Diagram - how to relate/join these tabels?

Posted on 2008-11-01
22
Medium Priority
?
1,211 Views
Last Modified: 2012-05-05
See Attachment:

As you can see I have a "ContactInformation" table
I then have two country fields
- Country (which is the country of the account holder)
- CountryOfCitzenship (as the name suggest this is the country of citizenship)

How do I join the yellow table with the orange table?
countryerd.jpg
0
Comment
Question by:Khou
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22856275
You create a second instance of Country, aliased say Country_1 or CountryCitizenship, and relate that to CountryOfCitizenship.

/gustav
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 300 total points
ID: 22856891
With Two instances of the Country table, you join the First instance to your Contact information table, on the Country field, and the second instance (CountryCitizenship) on the CountryOfCitizenship field.

You cannot have a single instance of Country, joined to both fields.
0
 
LVL 3

Expert Comment

by:johnsmith1962
ID: 22858562
I am pretty sure you can do that with ERwin.  That is use one table for both foreign keys.  This is really a question of  how your ER diagramming software works, not Oracle.  But if your software wont let you use one table for both fields, than the answer above will work.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Khou
ID: 22859183
Are both diagrams correct?
See Diagram ER A or ER B


countryerdlinked.jpg
countryerdlinked2.jpg
0
 

Author Comment

by:Khou
ID: 22859203
ER A:
Contactinformation table has two foreign keys
1 x FK CountryID (has 1 to 1 with Country table)
1 x FK CountryCitizenshipID (has 1 to 1 with Country table)


ER B:
Contactinformation table has two foreign keys
1 x FK CountryID (has 1 to 1 with Country table)
1 x FK CountryCitizenshipID (has 1 to 1 with CountryCitizenship table)

CountryCitizenship table has 1 foreign key
1 x FK CountryID (has 1 to 1 with Country table)
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1050 total points
ID: 22859213
Well, to be precise, you use one table but two instances of this.

I'm pretty sure Khou knows how to deal with the first instance for the country and explained how to add the second instance for the citizenship. Arthur describes the full process.

If an ER programming tool did not behave this way visually, it had to do so behind the scene.

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22859235
Neither is right.

Contactinformation table has two foreign keys
1 x FK CountryID (has 1 to 1 with Country table)
1 x FK CountryCitizenshipID (has 1 to 1 with Country table As CountryCitizenship)

CountryCitizenship (or any other name different from Country) is an alias for table Country.

/gustav
0
 

Author Comment

by:Khou
ID: 22859289
Hi Cactus

Cant figure out how to create Alias! (Using Visual Paradigm)
can you show me how it looks like with VP? or any other ER software.
0
 

Author Comment

by:Khou
ID: 22859298
why is diagram ER A and ER B wrong?
0
 

Author Comment

by:Khou
ID: 22860361
Hi Experts!

This is not going to work? (see diagram ER A, ER B)



Diagram ER A:ContactInformation Table
FK CountryID = 1111 <-- is FK of countryID
FK CountryOfCitizenshipID = 1111 <--- is FK of CountryID
Country Table
PK CountryID = 1111, name = Australia
PK CountryID = 7777, name = United Kingdom



Hence, ContactInformation table
Country = Australia
CountryOfCitizenship = Australia


-----------------------------------------------------------------------------------------------------
Diagram ER B:ContactInformation Table
FK CountryID = 1111 <-- is FK of countryID
FK CountryOfCitizenshipID = 12345 <--- is FK of CountryOfCitizenship

Country Table
PK CountryID = 1111, name = Australia
PK CountryID = 7777, name = United Kingdom

CountryOfCitizenship Table
PK CountryOfCitizenshipID = 12345, FK CountryID = 1111



Hence, ContactInformation table
Country = Australia
CountryOfCitizenship = Australia
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22860556
ER A will not do because you cannot have a single instance of Country joined to two fields in the same table (except if these for each record carried the same value which wouldn't make sense).

ER B assumes that some relation exists between country of address and country of citizenship which, I guess, is not the case neither here nor in general.

In most ER designers with a GUI you can drag and drop a table on the pane to create an instance of that table. This instance is typically given the name of the table, here Country. If you do it twice, the second instance is typically aliased with a trailing "_1", here Country_1. This alias (a property of the instance) you can typically later rename to what you prefer other than any other table name/alias already existing on the pane.

So, you should end up with something like this:

ContactInformation Table
FK CountryID = 1111 <-- is FK of countryID
FK CountryOfCitizenshipID = 77777 <--- is FK of CountryOfCitizenship

Country Table
PK CountryID = 1111, name = Australia
CountryOfCitizenship alias  <--- Country_1, alias for Country Table
PK CountryID = 7777, name = United Kingdom

/gustav
0
 

Author Comment

by:Khou
ID: 22860590
Understood, but unable to draw this with the tool,
ie "In most ER designers with a GUI you can drag and drop a table on the pane to create an instance of that table."

Such as ERWIN?

-how would you do this in SQL Management Studio? or
-Visual Paradigm?

By the way "CountryOfCitizenship alias  <--- Country_1, alias for Country Table" this is not a physical table is it!. im unable to create this.
0
 

Author Comment

by:Khou
ID: 22860600
by the way, was this a mistake? You have two Primary key for CountryID.


Country Table
PK CountryID = 1111, name = Australia
CountryOfCitizenship alias  <--- Country_1, alias for Country Table
PK CountryID = 7777, name = United Kingdom
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22860915
In Visual Studio you can do it in the DataSet designer, but that probably won't help you.

According to this, SQL Server Management Studio 2005 has a limitation:

http://www.windows-tech.info/15/a47ed0ff53dbd825.php

I don't know about Erwin - too pricey for me - but I'm confident it has this capability.

/gustav


0
 

Author Comment

by:Khou
ID: 22861223
Hi, to clarify

May I know when you create the "Country" and "Country_1" table, are they actually meaning the same underlying table, or 2 separated table actually?
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1050 total points
ID: 22861285
Yes, one "physical" Country table only, but two instances.

Even Microsoft Access can do this (not for modelling though, but for defining relations), so Erwin must have this ability too, for example for creating a self-join.

/gustav
0
 

Author Comment

by:Khou
ID: 22863562
I can create a self join in my software, but i thought you wanted to create a table alias?
am I understanding this wrong?

Could you draw up a simple diagram with this an alias? so I can see how it actually looks like!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22863643
Yes.
It is not a self-join, only that the visualization is much like this.

/gustav
countryerdlinked-1.jpg
0
 

Author Comment

by:Khou
ID: 22873754
cactus_data: why wouldnt this work? (see diagram).

erd.jpg
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22874083
I guess it would, but you would introduce a relationship between country of address and country of citizenship which I don't think exists. And if not, this setup will over-complicate things.
It may, however, be correct for your scenario. If you are unsure about this, your client must provide more information.

/gustav
0
 

Assisted Solution

by:Mariner73
Mariner73 earned 150 total points
ID: 22875147
Only a suggestion:
Can you put 2 FK into ContactInformation Table,
that points to Country tabe?
like in some previous coment:
Diagram ER A:
ContactInformation Table
FK1 CountryID = ---> is FK to countryID
FK2 CountryOfCitizenshipID = ---> is FK to CountryID

Or you can create in Erwin "Logical link" without any FK at least if you don't need them explicitly.
0
 

Author Closing Comment

by:Khou
ID: 31512288
Thanks guys
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question