April 10, 2008 - 12pm Central time zone
Reviewer: harsha pendyala
Thank you so much Tom for taking time to answer my question. It really helped me to understand how
to manage many-many relationships.
April 10, 2008 - 2pm Central time zone
Reviewer: David Aldridge from NOVA
"if your primary key included a date field (an excessively bad practice, to be frowned upon) "
What an intriguing comment ... :)
Followup April 10, 2008 - 2pm Central time zone:
the only time I think it would be "ok" would be in a dimension table in a warehouse star schema.
In a transactional system, I cannot imagine including the timestamp as part of the key.
Intersection qualities
April 10, 2008 - 8pm Central time zone
Reviewer: Michael O'Shaughnessy from Texas, USA
Of course it should be noted that the intersection table could have new intersect-dependent columns
in addition to the compound primary key. For instance, two tables: STUDENT and COURSE where the
STUDENT_COURSE table has an CLASSROOM column, CLASSROOM being dependent upon neither STUDENT nor
course but the intersection of the two.
Followup April 10, 2008 - 8pm Central time zone:
absolutely, association objects may have their own attributes - definitely.
April 11, 2008 - 8am Central time zone
Reviewer: Rajinder from Crystal City, VA USA
Tom,
A related question (and my apologies if it seems a bit divergent.) In the example quoted above, the
two "base" tables have a primary key each which become foreign keys in the intersection table. The
base table is indexed on PK's and the intesection table has a in-line constraints specifying
foreign keys as primary keys in the intersection table. Do I need to index these FKs or is Oracle
smart enough to use the index created in the base tables?
Thanks for your response.
Followup April 11, 2008 - 8am Central time zone:
how would an index on the base tables help in accessing rows in the intersection table.
typically, you query something like (using students and courses and an intersection of student_to_courses)
select *
from students s, students_to_courses sc, courses c
where s.student_name = 'TKYTE'
and s.student_id = sc.student_id
and sc.course_id = c.course_id;
Now, we would hope for an index on student_name - to find the row(s) in S fast.
We now have a set of student_ids for TKYTE.
We need to now access students_to_course to find the set of course_ids, we do that by student_id
Hence, we need an index on student_id in the SC table. We probably have that since student_id,course_id is the primary key - we have the primary key index.
However, we also frequently query the other direction:
select *
from students s, students_to_courses sc, courses c
where c.course_name = 'Intro to DBA'
and s.student_id = sc.student_id
and sc.course_id = c.course_id;
Here we start at course, goto SC by course_id - we need an index on course_id in SC therefore. We have an index on (student_id,course_id), but nothing that starts on course_id - so, we'd need to index that column - yes - in general
|