September 27, 2012 at 11:49 am
I have questions regarding foreign key and about parent-child relationship:
1. What is the purpose of self referential foreign keys ? or why do we create it?
2. In a many to many relationship, can a child table become a parent to another table?
September 27, 2012 at 12:00 pm
SQL_Loser (9/27/2012)
I have questions regarding foreign key and about parent-child relationship:1. What is the purpose of self referential foreign keys ? or why do we create it?
2. In a many to many relationship, can a child table become a parent to another table?
These sound like interview or homework questions. We can help point you in the direction but not many around here will just hand you an answer. Can you provide what you think the answer might be?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2012 at 12:03 pm
I have questions regarding foreign key and about parent-child relationship:
1. What is the purpose of self referential foreign keys ? or why do we create it?
To make a reference to the same table. This is usually found with hierarchies (eg. Employee table has a reference to itself to find immediate boss).
2. In a many to many relationship, can a child table become a parent to another table?
Yes, as long as the other table is a weak entity.
September 27, 2012 at 1:43 pm
Actually these are questions submitted to me by some coworkers taking a training course. I wanted to demonstrate the use of this resource for them.
September 27, 2012 at 1:50 pm
SQL_Loser (9/27/2012)
1. What is the purpose of self referential foreign keys ? or why do we create it?
As Luis mentioned above, Hierarchial Tables (think Org Charts) are the typical reason to use this, so you can't assign someone a non-existant manager. Similar structures, such as Bill of Materials tables, also use this technique.
2. In a many to many relationship, can a child table become a parent to another table?
Well, I'm not entirely sure where Luis was going with his answer, but the simple answer is yes, why not? Multiple level hierarchies are common. Imagine at the high level on one side of the many to many you have a product line. The product has categories, sub categories, and all sorts of other things. On the other side you have the client that bought it (also known as a sales order) and that client has company that they work for, etc etc.
Now, it can be argued in that example that it's actually a many to many between a pair of child tables, but it illustrates that entire hierarchies can exist on both sides of a many to many link... and was the first thing that came to mind.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 27, 2012 at 2:08 pm
For the many to many relationship, I might have a better example. I'm not sure if it's commonly used, but I've seen it.
If you have invoices and payments in a many to many relationship (one invoice can have several payments and one payment can affect multiple invoices), it's normal than these tables (entities in a relational design) have detail tables or weak entities (for the invoices, it will have the products contained in the invoice and for the payment can have cash, checks, etc).
I'm not sure if I'm making myself clear.
September 27, 2012 at 2:15 pm
Thank you all very much. This will help us to better understand these relationships. I am looking through Adventureworks database diagrams and I am able to see a self referential relationship.
Thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply