Home » Other » Training & Certification » Design problem (oracle 9i,release2,windows XP)
Design problem [message #285105] Mon, 03 December 2007 08:46 Go to next message
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
Member

Good day every body,

I have explained my desiging problem situation in below example.


eg:
patient
------
prac_id(pk)

location
-------

loc_id(fk)


course master
--------------
course_code(pk)
prac_id (fk)
loc_id(fk)


diploma master
--------------
deg_code(pk)
prac_id (fk)
loc_id (fk)


stud_trans
----------
stud_transid(pk)
deg_code(fk)
course_code(fk)

in this above tables stud_trans table contain two foreign key(deg_code and course_code) but

(i)some students have only deg_code
(ii)some students have only course_code
(iii) some students have both deg_code and course_code.

could u advice what is the way to design this deg_code and course_code within one table and also to satify
this 3 condition within one table.


- Thank you
Re: Design problem [message #285108 is a reply to message #285105] Mon, 03 December 2007 09:27 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
If I understand your question correctly, when defining the two columns in the stud_trans table, allow NULLS.

Re: Design problem [message #285111 is a reply to message #285105] Mon, 03 December 2007 09:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are the two related? (deg and course)
If not, there is no point in storing those relationships in a single table.
Re: Design problem [message #285114 is a reply to message #285105] Mon, 03 December 2007 10:11 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
To expand on what Frank said, you probably need two student tables, one that is the student_master, and one that is the student_courses. Just a thought
Re: Design problem [message #285116 is a reply to message #285114] Mon, 03 December 2007 10:39 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
Member


May i know how to allows null values in foreign key column.
Re: Design problem [message #285119 is a reply to message #285116] Mon, 03 December 2007 10:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Don't put a not null constraint on it
Re: Design problem [message #285120 is a reply to message #285116] Mon, 03 December 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULL values are valid in foreign keys.

Regards
Michel
Re: Design problem [message #285121 is a reply to message #285116] Mon, 03 December 2007 10:48 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Here is a very brief example. You have to explictly tell Oracle not to accept NULLS. The default is to allow NULLS.

CREATE TABLE STUDENT_TRANS
(
  STUD_TRANSID  NUMBER,
  DEG_CODE      NUMBER,
  COURSE_CODE   NUMBER
)
Re: Design problem [message #285122 is a reply to message #285121] Mon, 03 December 2007 10:58 Go to previous message
SRIVASUWIN
Messages: 45
Registered: October 2006
Location: TRICHY
Member


Thank you to all really this orafaq is Fabulous.
Previous Topic: selecting a best combination of numbers from a list of numbers
Next Topic: Query
Goto Forum:
  


Current Time: Sun Dec 04 04:58:07 CST 2016

Total time taken to generate the page: 0.05779 seconds