Khou
asked on
ER Diagram - how to relate/join these tabels?
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
ASKER
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
ASKER
why is diagram ER A and ER B wrong?
ASKER
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
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
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
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
ASKER
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.
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.
ASKER
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
Country Table
PK CountryID = 1111, name = Australia
CountryOfCitizenship alias <--- Country_1, alias for Country Table
PK CountryID = 7777, name = United Kingdom
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
Yes.
It is not a self-join, only that the visualization is much like this.
/gustav
countryerdlinked-1.jpg
It is not a self-join, only that the visualization is much like this.
/gustav
countryerdlinked-1.jpg
ASKER
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
It may, however, be correct for your scenario. If you are unsure about this, your client must provide more information.
/gustav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys
/gustav