Database design and foreign keys: Where should they be added in related tables?
- by Carvell Fenton
I have a relatively simple subset of tables in my database for tracking something called sessions. These are academic sessions (think offerings of a particular program). The tables to represent a sessions information are:
sessions
session_terms
session_subjects
session_mark_item_info
session_marks
All of these tables have their own primary keys, and are like a tree, in that sessions have terms, terms have subjects, subjects have mark items, etc. So each on would have at least its "parent's" foreign key.
My question is, design wise is it a good idea to include the sessions primary key in the other tables as a foreign key to easily select related session items, or is that too much redundency?
If I include the session foreign key (or all parent foreign keys from tables up the heirarchy) in all the tables, I can easily select all the marks for a session. As an example, something like
SELECT mark FROM session_marks WHERE sessionID=...
If I don't, then I would have to combine selects with something like
WHERE something IN (SELECT...
Which approach is "more correct" or efficient?
Thanks in advance!