| Author |
 Topic  |
|
|
DeliveryGuy
Starting Member
Australia
3 Posts |
 Posted - 05/15/2005 : 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
United Kingdom
12543 Posts |
Posted - 05/15/2005 : 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
Australia
3 Posts |
Posted - 05/15/2005 : 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
United Kingdom
12543 Posts |
Posted - 05/15/2005 : 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
Australia
3 Posts |
Posted - 05/16/2005 : 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
Australia
1591 Posts |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 05/16/2005 : 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. |
|
|
| |
Topic |
|
3 Tables have Many To Many each other
Forum Locked
