sohairzaki2005
asked on
One foreign key referencing primary key in multiple tables
Assignment table(assignmentid
pk,title,etc.,assignmentty pe(1=forum ,2=journal ,etc),refi d (id of a
forumthread,or id of a journal)
_______________________
forum table(threadid pk,threadtitle,graded y/n,etc.
________________
journal table(journalid,journaltit le,gradedy /n.etc.)
________________
is it ok to use refid as a foriegn key referencing forumid in fourm
table if the assignment type is forum
and refid is a foreign key referencing journalid in journal table if
assignment type is journal
how this can be accomplised in the best way to improve performance
I thought of doing the following
adding the following two tables journalassign(journalid,as signmentid )
and forumassign(forumid, assignm)
__________________________ _
I thought of another alternative please let me know the best way to go
for it
Assignment table(assignmentid
pk,title,etc.,assignmentty pe(1=forum ,2=journal ,etc),refi d (id of a
forumthread,or id of a journal)
foreign key1 is (assignmenttype+refid) referencing threadi+type in forum
table
foreign key2 is (assignmenttype+refid) referencing journalid+type in
journal table
forum table((threadid +Type=1) pk,threadtitle,graded y/n,etc.
and
________________
journal table((journalid+type=2)pk ,journalti tle,graded y/n.etc.)
________________
but I have similar situations in other tables
example:
lessontools(lessonid + toolid pk, tooltype (1=dropbox,2=,etc.)
dropbox(dropboxid, etc)
___________________
Please see attached diagram and give me your opinion
thanks
pk,title,etc.,assignmentty
forumthread,or id of a journal)
_______________________
forum table(threadid pk,threadtitle,graded y/n,etc.
________________
journal table(journalid,journaltit
________________
is it ok to use refid as a foriegn key referencing forumid in fourm
table if the assignment type is forum
and refid is a foreign key referencing journalid in journal table if
assignment type is journal
how this can be accomplised in the best way to improve performance
I thought of doing the following
adding the following two tables journalassign(journalid,as
and forumassign(forumid, assignm)
__________________________
I thought of another alternative please let me know the best way to go
for it
Assignment table(assignmentid
pk,title,etc.,assignmentty
forumthread,or id of a journal)
foreign key1 is (assignmenttype+refid) referencing threadi+type in forum
table
foreign key2 is (assignmenttype+refid) referencing journalid+type in
journal table
forum table((threadid +Type=1) pk,threadtitle,graded y/n,etc.
and
________________
journal table((journalid+type=2)pk
________________
but I have similar situations in other tables
example:
lessontools(lessonid + toolid pk, tooltype (1=dropbox,2=,etc.)
dropbox(dropboxid, etc)
___________________
Please see attached diagram and give me your opinion
thanks
ASKER
I do not think this is a good idea.
Thanks
Thanks
Would you mind sharing why you think that my suggestion is not a good idea?
Another option that would be acceptable but less interesting that my earlier suggestion, in my opinion, is that you create two tabes: JournalAssignment and ForumAssignment.
As for LessonTools: why would you store ToolType there? It seems to me that ToolType is an attribute of "Tool" and thus belongs in the Tool table (which is referred to through ToolID)?
Another option that would be acceptable but less interesting that my earlier suggestion, in my opinion, is that you create two tabes: JournalAssignment and ForumAssignment.
As for LessonTools: why would you store ToolType there? It seems to me that ToolType is an attribute of "Tool" and thus belongs in the Tool table (which is referred to through ToolID)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, I notice that your primary keys are sometimes made up of several fields, for instance your Journal table's PK is JournalID and ToolType. That seems a bit weird. Isn't your JournalID unique across all records?