Solved

One foreign key referencing primary key in multiple tables

Posted on 2011-03-22
4
579 Views
Last Modified: 2012-06-22
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
Comment
Question by:sohairzaki2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35196283
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
 

Author Comment

by:sohairzaki2005
ID: 35199122
I do not think this is a good idea.
Thanks
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35205547
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
 
LVL 5

Accepted Solution

by:
bitref earned 500 total points
ID: 35207034
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query group by data in SQL Server - cursor? 3 64
Add a step to a system backup job 6 32
Stored Proc - Rewrite 42 72
Need return values from a stored procedure 8 40
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question