June 17, 2010 at 10:23 am
I am in a DB design phase. Encountered a situation where 2 major entities have many to many relationship.
Yes i know we need a junction table. But i dont understand why we cant have PKs as some unique IDS to both, have one tables PK as another tables FK and pull the required information saying..
SELECT ...... FROM Table1 T1 JOIN Table2 T2 on T1.PK = T2.FK
1.
Can you please and please give me an example/s which slaps my confusion and says heres why we say JUNCTION TABLE Is indispensable.
2. I read this article which talks about man to many relationships
http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php
In the last paragraph which says 'Data Representation' says that
Many-Many relationship between Orders and Products evolved into a
1-many b/w Orders and OrderLines and
many-1 b/w OrderLines and Products.
Here i understand that OrderLines is a Junction table ..Ok accepted .. BUT
What if i have many 'many to many relationships' like OrderLines.
If i have more info than OrderLines which is evolving out of many-many relationships between Orders and Products where do i house them ?
Do i represent that new table under OrderLines having similar mappings from orders and Products
THANKS FOR READING THROUGH, ANY HELP FROM LEARNED SENIORS IS MORE THAN WELCOME
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
June 17, 2010 at 10:59 am
It seems you are resistant to junction tables, so try it without them in a dev environment and see if it works for you.
There is no law that says you must use established design patterns.
June 17, 2010 at 1:38 pm
In the relational model all relationships can be represented in only one way: in tables. So a table called T with columns X and Y indicates that there is a relationship between X and Y.
Now suppose that X represents a thing that has other attributes dependent on it: the attributes A,B and C for example. If the relationship between X and Y is exactly one to one (formally called a bijection) then A,B,C can easily go into table T along with X and Y.
A problem arises if the relationship between X and Y is 1 to n (where n is more than 1). Then you would have to repeat the X value multiple times in the same table and you would therefore have to repeat values for A,B and C as well. That creates redundancy which is ambiguous, inefficient and can lead to incorrect results. So that's why we apply the principles of Normalization, move A, B and C into a new table and apply a foreign key constraint to ensure that the correct integrity rule applies. The same principle applies (doubly so) to a n to m relationship, where both sides are greater than 1.
Regarding your example query. I'm not clear what your intention is or why you think it represents a many to many relationship. Maybe it would help if you show us some sample data and what result you expect to get from it. If you post table structures then please make sure you indicate what keys exist in your table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply