SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 3 Tables have Many To Many each other
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

DeliveryGuy
Starting Member

Australia
3 Posts

Posted - 05/15/2005 :  07:57:05  Show Profile  Visit DeliveryGuy's Homepage
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

United Kingdom
12543 Posts

Posted - 05/15/2005 :  09:28:12  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

DeliveryGuy
Starting Member

Australia
3 Posts

Posted - 05/15/2005 :  12:18:46  Show Profile  Visit DeliveryGuy's Homepage
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?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/15/2005 :  16:21:13  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

DeliveryGuy
Starting Member

Australia
3 Posts

Posted - 05/16/2005 :  01:32:53  Show Profile  Visit DeliveryGuy's Homepage
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~!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 05/16/2005 :  02:02:14  Show Profile
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 05/16/2005 :  08:10:29  Show Profile  Visit nr's Homepage
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000