Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

One foreign key referencing primary key in multiple tables

Assignment table(assignmentid

pk,title,etc.,assignmenttype(1=forum,2=journal,etc),refid (id of a

forumthread,or id of a journal)

_______________________

forum table(threadid pk,threadtitle,graded y/n,etc.

________________

journal table(journalid,journaltitle,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,assignmentid)

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.,assignmenttype(1=forum,2=journal,etc),refid (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,journaltitle,gradedy/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 Data diagram
0
sohairzaki2005
Asked:
sohairzaki2005
  • 2
1 Solution
 
ValentinoVCommented:
In your Assignment table I would create two foreign keys: ForumID to refer to your Forum table and JournalID to refer to yourJournal table.  When AssignmentType = 1 then ForumID should be filled in.  And when type=2 then JournalID should be filled in.

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?
0
 
sohairzaki2005Author Commented:
I do not think this is a good idea.
Thanks
0
 
ValentinoVCommented:
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)?
0
 
bitrefCommented:
You may create a new table as an intermadiate link in which you store the many-to-many relationship foreign key data.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now