Ok, This is going to sound amateurish because it is. I am having issues with a many to many relatioship. Now it is not me having issues but by request of my boss that needs to be convinced. When you create a many to many relatioship you create 3 tables. You populate the 2 outer tables and you enter the primary keys from the outer tables in the X-ref table as primary keys being foreign keys to the outer tables. My question is(Then again this is to show proof that I am not going crazy and I can show some proof to others) Do you have to populate the cross-ref table with the data from the outer tables? and would you enter NEW extra fields that are not related to the outer tables in any kind of way. An Answer to this from more of you designers would help my fighting cause and allow me to develop and design correctly as I would prove some people wrong. Thank you so much
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Basic Database Design question. on a Many to many relationship
- Thread starter inAwww
- Start date
Yes to everything.
The inner table is sometimes called a join table or a relationship table.
It does have to be populated with the PK data from the other 2 tables.
Sometimes other columns can be put in there too. Common ones to see are the Date/Time that relationship was entered, perhaps Who/What user entered it, a Notes column about the relationship. Anything that specifically defines the relationship between FK1-FK2. To be considered normalized, these other new columns must depend on the whole[/] primary key: both columns, not just one.
hope that helps to get you started?
bperry
The inner table is sometimes called a join table or a relationship table.
It does have to be populated with the PK data from the other 2 tables.
Sometimes other columns can be put in there too. Common ones to see are the Date/Time that relationship was entered, perhaps Who/What user entered it, a Notes column about the relationship. Anything that specifically defines the relationship between FK1-FK2. To be considered normalized, these other new columns must depend on the whole[/] primary key: both columns, not just one.
hope that helps to get you started?
bperry
I'm not sure which you want comments on and answers to so...
When you create a many to many relatioship you create 3 tables. You populate the 2 outer tables and you enter the primary keys from the outer tables in the X-ref table as primary keys being foreign keys to the outer tables.
Yes.
Do you have to populate the cross-ref table with the data from the outer tables?
No.
Why would you duplicate the data when you have a perfectly good reference to it. You might want to do it to speed up processing time later, but not during the initial design. Adding the data to the xref table would be poor normalization.
would you enter NEW extra fields that are not related to the outer tables in any kind of way.
No. I think.
You would not want to add extra UNRELATED FIELDS to the xRef table, but you may want to add RELATED FIELDS to the xRef table. It depends on what your data is.
A good example would be a schools student and class database. Three tables: STUDENT, CLASS, and STUCLASS.
STUDENT
[ul][li]sid[/li][li]sname[/li][/ul]
CLASS
[ul][li]cid[/li][li]cname[/li][/ul]
STUCLASS
[ul][li]sid[/li][li]cid[/li][li]passfail[/li][/ul]
The passfail item is related to both the parent tables. However, having said that, I would not go putting something like the student's birthday in STUCLASS, that belongs to the student, not the class. I would not put the names of the class or the student in the STUCLASS table either, that is poor normalization. Getting a list of all students and which classes they took is simple enough:
Hope this helps
When you create a many to many relatioship you create 3 tables. You populate the 2 outer tables and you enter the primary keys from the outer tables in the X-ref table as primary keys being foreign keys to the outer tables.
Yes.
Do you have to populate the cross-ref table with the data from the outer tables?
No.
Why would you duplicate the data when you have a perfectly good reference to it. You might want to do it to speed up processing time later, but not during the initial design. Adding the data to the xref table would be poor normalization.
would you enter NEW extra fields that are not related to the outer tables in any kind of way.
No. I think.
You would not want to add extra UNRELATED FIELDS to the xRef table, but you may want to add RELATED FIELDS to the xRef table. It depends on what your data is.
A good example would be a schools student and class database. Three tables: STUDENT, CLASS, and STUCLASS.
STUDENT
[ul][li]sid[/li][li]sname[/li][/ul]
CLASS
[ul][li]cid[/li][li]cname[/li][/ul]
STUCLASS
[ul][li]sid[/li][li]cid[/li][li]passfail[/li][/ul]
The passfail item is related to both the parent tables. However, having said that, I would not go putting something like the student's birthday in STUCLASS, that belongs to the student, not the class. I would not put the names of the class or the student in the STUCLASS table either, that is poor normalization. Getting a list of all students and which classes they took is simple enough:
Code:
select sName, cName
from class as c, student as s, stuclass as sc
where c.cid = sc.cid and
s.sid = sc.sid and
order by sName, cName- Thread starter
- #4
- Status
Similar threads
- Locked
- Question
- Locked
- Question