3 Tables have Many To Many each other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

3 Tables have Many To Many each other

Author

Topic

DeliveryGuy
Starting Member

3 Posts

Posted - 2005-05-15 : 07:57:05

There is 3 entities: A, B, C
---------------------------
A (Aid, AA)
B (Bid, BB)
C (Cid, CC)

[Note: AA,BB, CC is the attributes of the 3 entities individually]

---------------------------
A - B : Many to Many
A - C : Many to Many
B - C : Many to Many
---------------------------

Which one is correct in the following table design?

1)
A(Aid, AA)
B(Bid, BB)
C(Cid, CC)
AB(Aid, Bid)
AC(Aid, Cid)
BC(Bid, Cid)

2)
A(Aid, AA)
B(Bid, BB)
C(Cid, CC)
ABC(Aid, Bid, Cid)

Which one is truly correct?
How about another?
Could give me the difference between answer 1) and answer 2)?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-15 : 09:28:12

Depends on the requirements
2 is is suggesting that BC is dependent on AB
1 doesn't
i.e. if you have
1,1,1
then drop the AB join does this invalidate the AC and BC joins?
if so then go for 2 and include all possible combinations of joins in the join table.
otherwise 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DeliveryGuy
Starting Member

3 Posts

Posted - 2005-05-15 : 12:18:46

Hi, nr

I still have the mistiness about the two cases

e.g I take an example to show my problem. (Can i choose answer 2 to draw the following case?)

Student(StudentID, StudentName)
Lecturer(LecturerID, LecturerName)
Subject(SubjectID, SubjectName)

These 3 entities have the M-M relationship each other.
So I get a mid-table to record these 3 tables' relationship

Student_Lecturer_Subject(StudentId, LecturerId, SubjectId)------------------>Can i make this?

if i make this table, every 1 row can show one case about these 3 entities.

----------------------------------------------------------------------------------------------------------

if i make 3 mid-tables to show the 3 entities, i have to insert 3 rows into the following tables every one case. Does the following have efficiency when operate the 3 entites?

Student_Lecturer(StudentId, LecturerId)
Student_Subject(StudentId, SubjectId)
Lecturer_Subject(LecturerId, SubjectId)
----------------------------------------------------------------------------------------------------------

How can we get a general rule to define this kind of cases when designing database?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-15 : 16:21:13

You would probably need
lecturer - subject
then does the student take a subect?
subject - student
or do they take a subect with a lecturer
lecturer, subject - student

Depends on the requirements you are modelling.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DeliveryGuy
Starting Member

3 Posts

Posted - 2005-05-16 : 01:32:53

hi, nr

Can i follow the following point of views to design the tables?

1) We should find the most basic entity which has the strong relation with other entities?
2) Every two entities get a relationship to make a mid-table, then make this mid-table relate with another entity?

could you give me some good complicated cases or reference?

Thank you a lot~!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-16 : 02:02:14

Delivery Guy,

You are describing a ternary relationship...

http://www.databasedesignstudio.com/faq/Ternary.htm

DavidM

A front-end is something that tries to violate a back-end.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-16 : 08:10:29

Only if that's what the data represents.

Consider in your example if the student is taking a subject but that subject could be taught by any number of lecturers, but the subject itself has identity independent of the students and lecturers.
Then you would need AB BC conjoint tables to model the lecturers and students associated with the subject.
You might need other entities - maybe period based to show when lecturers and students are present at the subject and that would give you the student/lecturer association.

Probably simpler is colleges A having courses B and students C.
That would probably AB AC AB-AC situation.
i.e.
a student can enroll at a college without enrolling in a course (but probably would do at some point)
A college can have courses with no students
A student at a college will enroll in a course at that college.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources